-1

My data model has 4 tables Storage, Emissions, Infrastructure, and Projects.

The Projects table references the other three tables with many-to-many relationships, and hence I assumed using a Junction table is the logical way forward.

The planned Junction table has the following structure:

enter image description here

My issue is that there are cases where a Project can have no values for Emission, Infrastructure, or Storage. It will always have a value for at least one of these columns, but not always all of them.

I know that junction tables use composite primary keys and hence cannot have null values for its columns. Is there an alternative to this?

P.S. The suggested solution for How can you represent inheritance in a database? cannot be used in my case, as each of these four tables have standalone data and relationships to other tables not mentioned. The question here is solely about representing many-to-many relationships.

Vishnu Prasad
  • 73
  • 1
  • 9
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Dec 15 '22 at 11:05

1 Answers1

1

Use a separate junction table for each many to many relationship.

ysth
  • 96,171
  • 6
  • 121
  • 214
  • I know this will work, but it's not an elegant solution and complicates the schema. – Vishnu Prasad Dec 19 '22 at 12:38
  • anything else will cause you nightmares later. It is the elegant solution; what you propose is much more of a hack. – ysth Dec 19 '22 at 18:40
  • just a few examples of the complexity of your proposed table: if a project has multiple storage and emissions, which pairings of those two go in which junction rows? presumably you don't have separate rows for all possible pairings? if a relationship is removed, you can't just null out a field, then you end up with multiple rows with all nulls for the relationships and joined selects produce extra rows. so you need logic to tell whether to update or delete a junction row. and your junction table would need indexes for all the different relationships. separate junctions is so much cleaner. – ysth Dec 19 '22 at 18:46