0

I'm trying to figure out if it is bad practice to have more than one path between tables that are related via many-to-many relationships.

I have 3 user-facing tables: Dupe, Cue, and Take. There is a many-to-many relationship between Dupes and Cues, and there is a many-to-many relationship between Takes and the join table Dupe_Cues.

database schema diagram

The user can add, delete, and modify Cues, Dupes and Takes, and she can make links between Cues/Dupes and Cues/Takes, but there are also certain situations when the user will want to directly add a Take to a Dupe, without creating a cue. So I figure the simplest thing to do is create a Dupe_Take table. The problem is, I'm worried it will cause referential issues and isn't good database design.

If I don't create a Dupe_Take table, I suppose I could add a field to the Cue table, like "b_CreatedByUser". Then, whenever the user does a query, I filter out Cues where b_CreatedByUser=FALSE so that Cues that are only used to link Takes to Dupes are never shown to the user, but that sounds sloppy and difficult to maintain.

What is the best practice? What factors should I be considering?

Thanks for any advice.

  • there are also certain situations when the user will want to directly add a Take to a Dupe, without creating a cue. If so your referential scheme is wrong. There is no "major" and "minor" references. – Akina Apr 11 at 6:26
  • @Akina thanks for the comment. I guess I need to rethink the role of the Cue table. – slondike Apr 12 at 5:17
  • You need one table stored joining triplets I think... where not more than one NULL for a record is allowed. – Akina Apr 12 at 5:20

Your Answer

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

Browse other questions tagged or ask your own question.