Postgresql – When choosing which structural table design for one-to-many relations

database-designpostgresql

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

Best Answer

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
Related Question