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.

Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jan 24 '23 at 22:07
This writing is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. It is not clear what you want to accomplish. It is not clear what you want to ask. Please ask exactly 1 (specific researched non-duplicate) question. Not multiple questions. What is stopping you from making 1 table? A table like what? Are you asking for a query? "normalize into one table" is not clear. Reorganizing for a supertype/inheritance is not DB (de)normalization. Please write out what you mean instead of (mis)using that 1 word. [mre] [ask] [Help] — philipxy, Jan 25 '23 at 01:16
I am not mixing anything up. Inheritance involves subtyping. The duplicate question title uses the word "inheritance" but the Q&A is about subtyping. So does this question. A subcategory is a (sub)type of thing that includes both categories & subcategories. There are many many posts about DB design for subtyping/inheritance/polymorphism & hierarchical relation(ship)s/associations. — philipxy, Jan 25 '23 at 21:05
@philipxy. Where you are talking about? In the title there is no single word of "inheritance" written! — mightycode Newton, Jan 26 '23 at 07:47
"The duplicate question title". The question in my 1st comment. The comment produced by the system by my flagging this as a duplicate question. — philipxy, Jan 26 '23 at 07:50

Hogan · Accepted Answer · 2023-01-24T15:17:07.577

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

Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/251367/discussion-between-hogan-and-mightycode-newton). — Hogan, Jan 24 '23 at 15:39

How to normalize three identical tables into one table?

1 Answers1