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_b
referencestable_a
directly 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?
Edit:
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
database-design
wording matters. It's about logic, it stems from first-order predicate logic. Once you describe the "business problem" (problem domain) using predicates and constraints, the model (schema) simply follows. There is no way saying which one of these is logically correct for a given problem domain using generic "tableology".