How to normalize three identical tables into one table?-CodePudding

I have three tables, like:

cat1:

id(PK)
name
description

cat2:

id(PK)
name
description
cat1_id1(FK)

cat3

id(PK)
name
description
cat2_id(FK)

and the relationship is: cat1 has one-to-many cat2, and cat2 has one-to-many cat3.

So my question is. How to normalize the three tables into one table?

So for example this design. the principle is the same:


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 looks like:

for example:

animal group has mammals.
Then mammals(can have more categories) has category: cats, dogs, etc.
Then the cats category(can have more subcategories) has subcategory: little cats, big cats fore eample.
And then the little cats(subcategories can have more animals) has the real cat specie: ragdoll.

I hope you understand.

So this design is correct? I mean they have all four the same fields.

But this is oke? And so if I want to add one more field, for example age. Then in all four tables I have to add the field age.

CodePudding user response：

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