2

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:

  1. For A and B, I could create two tables, whereas table_a references table_a directly via a foreign key. I thinks, 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)
    );
    
  2. 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
8
  • 2
    Can you provide more context to what data you're actually trying to store? I can't look at "table_a" and "table_b" and "id" this and "id" that and have any clue how to solve your problem. – bbaird Dec 9 '20 at 15:00
  • Both tables have different columns with different data. A and B have different columns. Important is that the records of B are structural children of A. So, I am searching for arguments for either option 1 or 2 to link both tables. From my point of view, both are feasible. I am not quite sure what you need to solve the problem. – S-Man Dec 9 '20 at 15:04
  • What does "structural children of A" mean? – bbaird Dec 9 '20 at 15:06
  • As shown at the top of the question, you can think of A and B as a parent-child tree relation. – S-Man Dec 9 '20 at 15:07
  • 1
    In 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". – Damir Sudarevic Dec 9 '20 at 16:00
2

D |-[0,*]----------[1]-| H

Each door belongs to exactly one house;
for each house: more than one door may belong to that house.

-- House H exists.
--
house {H}
   PK {H}


-- Door D belongs to house H.
--
door {D, H}
  PK {D}
  FK {H} REFERENCES house {H}

D |-[0,*]--------[0,1]-| H

Each door belongs to at most one house;
for each house: more than one door may belong to that house.

-- House H exists.
--
house {H}
   PK {H}


-- Door D exists.
--
door {D}
  PK {D}


-- Door D belongs to house H.
--
house_door {H, D}
        PK {D}
       FK1 {H} REFERENCES house {H}
       FK2 {D} REFERENCES door  {D}

Note that:

D |-[1,*]----------[1]-| H

Each door belongs to exactly one house;
for each house: at least one door belongs to that house.

is not as easy to achieve. This may look OK:

-- Door D belongs to house H.
--
house_door {D, H}
        PK {D}


-- House H exists.
--
CREATE VIEW house (H)
AS
SELECT DISTINCT H
FROM house_door;

however, it is not possible to have a FK to a house from the rest of the model.


All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key
2
  • Thank you. So, I guess you'd prefer approach 1 from my question over option 2. Can you argument this? :) – S-Man Dec 10 '20 at 8:07
  • @S-Man Not really, I do not prefer first over second -- depends on constraints. The first one is "classic" one-to-many. – Damir Sudarevic Dec 10 '20 at 11:53
2

I think some of the confusion is in how you labeled things. Object A and Object B are different from Table A and Table B, because as bbaird points out, Object A and Object B are the same Object (or entity in the context of databases).

From my understanding of your problem description you can think of your data as if it were actual Parents and Children for a second to help conceptualize. Parents could go into one Table and Children in a separate Table but they share close enough characteristics because both are People and probably should live in the same primary Table. Then you can have a second Table that represents the direct relationship of a Child to its Parent. After thinking of it this way, your first design choice makes more sense (which is a pretty common design choice for a Parent-Child relationship set of data.)

Note: The above answer was prior to OP updating his question. Please see the accepted answer for further clarification.

1
  • No, in that case, parent and children are not of same type. It is more like "house" and "doors". A house has some doors, a door is in a house. – S-Man Dec 9 '20 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.