How to implement many-to-many relationship with shared foreign keys?

userKuyX 0
Viewed 15

I am struggling with modelling my database so that entities can only have a many-to-many relationship if they share the same "parent" entity.

Here's an example:

  • Folders have a one-to-many relationship with Presentations.
  • Folders have a one-to-many relationship with Photos.
  • Presentations and Photos have a many-to-many relationship.

Is there a way of enforcing that Presentations and Photos are only related if they belong to the same Folder - that they share the same foreign key?

database-design database
1 Answers

Folder holds the many-to-many relationship - related presentations and photos share the same folder, so:

create table folder (
    id int,
    -- other columns
);

create table presentation (
    id int,
    folder_id int not null references folder,
    -- other columns
);

create table photo (
    id int,
    folder_id int not null references folder,
    -- other columns
);

Join presentations and photos using folder_id.

As an example, to find photos related to a presentation:

select ph.*
from presentation pr
join photo ph on ph.folder_id = pr.folder_id
where pr.id = ?
Bohemian0
Related