How to normalize three identical tables into one table?

Question

I have three tables:

cat1:

id(PK)
name
description

cat2:

id(PK)
name
description
cat1_id1(FK)

cat3

id(PK)
name
description
cat2_id(FK)

cat1 has one-to-many cat2, and cat2 has one-to-many cat3.

How do I normalize the three tables into one table?

For example this design:

CREATE TABLE IF NOT EXISTS public."animalGroups_animalgroup"
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY , 
    name    text
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,   
)

CREATE TABLE IF NOT EXISTS public.category_category
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,   
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,   
    animalgroup_id(FK)
)

CREATE TABLE IF NOT EXISTS public.subcategory_subcategory
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,   
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL, 
    category_id(FK)
)

CREATE TABLE IF NOT EXISTS public.animal_animal
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,  
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,
    subcategory_id(FK)
)

• animalgroup can have one or more categories
• category can have one or more subcategory
• subcategory can have one or more animal

How it will look like:

• animal group has mammals
• mammals (can have more categories) has for example categories cats, dogs
• cats category (can have more subcategories) has for example subcategories little cats, big cats
• little cats (subcategories can have more animals) has the real cat species ragdoll

Is this design correct?

They have four of the same fields. To add one more field, for example age, then in all four tables I have to add the field age.

Answer 1

Ok you changed your DB design so that would like like this:

 SELECT * -- should specify columns here
 FROM cat1
 LEFT JOIN cat2 on cat1.id = cat2.cat1_id1
 LEFT JOIN cat3 on cat2.id = cat3.cat2_id

The difference in naming (cat1_id1 vs cat2_id) is strange -- I think that 1 might be a typo.

---

original answer below

I'm guessing your tables actually look like this

cat1:

id
cat2id
name
description

cat2:

id
cat3id
name
description

cat3

id
name
description

Where the 1 to many relationship is represented by the id they are related to in the columns I added.

In that case you can join them like this

SELECT * -- should have column list here
FROM cat1
LEFT JOIN cat2 on cat1.cat2id = cat2.id
LEFT JOIN cat3 on cat2.cat3id = cat3.id