I am facing a problem. I have to design a database schema table structure. Generally I have a small hierachical tree structure with one-to-many relations:
A |-B1 | |-C1 | |-C2 | |-B2 |-C3 |-C4
Now, from my point of view, I have two options to design this:
For A and B, I could create two tables, whereas
table_adirectly via a foreign key. I think, this could be the better approach because you'll need less join operations on queries.
CREATE TABLE table_a ( id int PRIMARY KEY, some_specific_data text ); CREATE TABLE table_b ( id int PRIMARY KEY, some_other_specific_data text, id_parent int REFERENCES table_a(id) );
For A and B I could create two independent tables and an additional relation table which connects both tables (as I would do for many-to-many relations). Here both tables are more independent, which, I believe, should be desirable in general.
CREATE TABLE table_a ( id int PRIMARY KEY, some_specific_data text ); CREATE TABLE table_b ( id int PRIMARY KEY, some_other_specific_data text ); CREATE TABLE a_to_b ( id int PRIMARY KEY, id_a int REFERENCES table_a(id), id_b int REFERENCES table_b(id) );
From my point of view, both are valid designs. However, I am not able to decide which one should be chosen. Are there any arguments for choosing either option 1 or option 2 or even a third option?
To be more specific:
A and B are not of the same type. You can think of it as:
- A is a house
- B is a door
- So, a house has many doors
- a door is contained by one house