How to model a complex relationship involving a Weak Entity and entities related to its parents?

Question

I'm working on a database schema involving four tables: A, B, C, and D. The relationships among these tables are as follows:

• Table A can have one or many records from Table B.
• Table C can have one or many records from Table D.
• There is a many-to-many relationship between Tables A and C, forming a weak entity (joint table) "AC".

CREATE TABLE A (
    id INT PRIMARY KEY
);

CREATE TABLE B (
    id INT PRIMARY KEY,
    a_id INT,
    FOREIGN KEY (a_id) REFERENCES A(id)
);

CREATE TABLE C (
    id INT PRIMARY KEY
);

CREATE TABLE D (
    id INT PRIMARY KEY,
    c_id INT,
    FOREIGN KEY (c_id) REFERENCES C(id)
);

CREATE TABLE AC (
    a_id INT,
    c_id INT,
    PRIMARY KEY (a_id, c_id),
    FOREIGN KEY (a_id) REFERENCES A(id),
    FOREIGN KEY (c_id) REFERENCES C(id)
);

My requirements : For each AC record, I need to link the Bs associated with its A to the Ds associated with its C.

Explanations : This looks like a ternary relationship involving AC, B and D : In the context of an AC record, a B needs to be linked to a D. But we have the added constraints of the B needing to belong to the A of the AC record, and the D needing to belong to the C of the same AC record.

What I've tried : I've tried creating a simple "assignment" table which references AC, B and D. However, I couldn't enforce the constraints mentioned above.

CREATE TABLE assignment (
    id INT PRIMARY KEY,
    ac_a_id INT,  -- Foreign key referencing 'a_id' in AC
    ac_c_id INT,  -- Foreign key referencing 'c_id' in AC
    b_id INT,     -- Foreign key referencing 'id' in B
    d_id INT,     -- Foreign key referencing 'id' in D

    FOREIGN KEY (ac_a_id, ac_c_id) REFERENCES AC(a_id, c_id),
    FOREIGN KEY (b_id) REFERENCES B(id),
    FOREIGN KEY (d_id) REFERENCES D(id)
);

What would be a good approach to model this relationship? Are there specific SQL constructs or design patterns that would best fit this scenario?

Thanks for your help.

Answer 1

While AC is a weak entity, it is more properly referred to as an associative table (also called a bridge table). There isn't any need for an "assignment" table referencing AC, B, and D, you've already modeled the relationship between B and D. To demonstrate, run the following commands to create and populate the tables:

CREATE TABLE a (
  id integer PRIMARY KEY
);

CREATE TABLE b (
  id integer PRIMARY KEY,
  a_id integer REFERENCES a(id)
);

CREATE TABLE c (
  id integer PRIMARY KEY
);

CREATE TABLE d (
  id integer PRIMARY KEY,
  c_id integer REFERENCES c(id)
);

CREATE TABLE ac (
  a_id integer REFERENCES a(id),
  c_id integer REFERENCES c(id),
  CONSTRAINT ac_pk PRIMARY KEY (a_id, c_id)
);

INSERT INTO a(id)
SELECT generate_series(100, 109);

INSERT INTO b(id, a_id)
VALUES (200, 100),
       (201, 101),
       (202, 101),
       (203, 103);

INSERT INTO c(id)
SELECT generate_series(300, 309);

INSERT INTO d(id, c_id)
VALUES (400, 300),
       (401, 301),
       (402, 302),
       (403, 302);

INSERT INTO ac(a_id, c_id)
VALUES (100, 300),
       (101, 301),
       (102, 301),
       (103, 302),
       (104, 302),
       (104, 303);

The following query returns the associated rows from B and D through AC:

SELECT ac.a_id, b.id AS b_id, ac.c_id, d.id AS d_id
  FROM ac
  JOIN b ON ac.a_id = b.a_id
  JOIN d ON ac.c_id = d.c_id
  ORDER BY ac.a_id, b.id, ac.c_id, d.id;

The output from the preceding query is:

a_id	b_id	c_id	d_id
100	200	300	400
101	201	301	401
101	202	301	401
103	203	302	402
103	203	302	403

The following query demonstrates the "link" between B and D:

SELECT b.id AS b_id, d.id AS d_id
  FROM ac
  JOIN b ON ac.a_id = b.a_id
  JOIN d ON ac.c_id = d.c_id
  WHERE b.id = 203
  ORDER BY b.id, d.id;

The output shows that the row in B with id = 203 is associated with D rows with ids of 402 and 403:

b_id	d_id
203	402
203	403