What are the pros/cons of between these 2 database design options where the join 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 (join 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 (join 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)

Potential Queries:

Get all labels associated with a user
Get all properties associated with a label of a user

Example data:

User1 has labels "old" and "new" for property "bicycle".
User2 has labels "1 year", "2 years", and "3+ years" for property "bicycle".
User 3 has labels "<3 years" and "3+ years" for property "car"
User 4 has labels "red", "blue", and "black" for property "car"
User 5 has labels "daily beater" and "weekend fun" for property "car" and labels "<3 years" and "3+ years" for property "bicycle"
User 6 has labels "1 year", "2 years", and "3+ years" for properties "bicycle" and "car"

The list of properties is large, but finite. Users will reference the same property and cannot have their own variation in its spelling. So for example, “bicycle” will always be “bicycle” for every user. A user cannot have “bike” or “bi-cycle” or some other spelling of the property.

The labels are all user defined so it can be infinite depending on how each individual user wants to label their properties.