Categories
Database Development

What are the pros/cons of between these 2 database design options where the through table can have 2 or 3 foreign keys?

I am trying to model the following scenario, but I have come up with 2 options and I cannot figure out how to decide between the 2 options. Both options seem to work, but I worry that I am missing some edge case that suggests one over the other. Any recommendations or suggestions on where one option might be better than the other option?

Thank you for your time 🙂

The model:

A user has many labels
A property has many labels
A label has many properties

Option 1 (through table with 2 foreign keys):

user
----
id
name

property
--------
id
name

label
-----
id
name
user_id (foreign key for user.id)

property_label
--------------
property_id (foreign key for property.id)
label_id (foreign key for label.id)

Option 2 (through table with 3 foreign keys):

user
----
id
name

property
--------
id
name

label
-----
id
name

user_property_label
-------------------
property_id (foreign key for property.id)
label_id (foreign key for label.id)
user_id (foreign key for user.id)

Leave a Reply

Your email address will not be published. Required fields are marked *