0

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.

Update:
While musing on Jim's comments, the solution came.

The join table with 2 foreign keys is better because it is further normalizing the data.

For instance, this scenario:

User1 has labels "old" and "new" for property "bicycle"
User1 has labels "old" for property "car"

will produce this data using the option with 3 foreign keys:

user (id, name)
---------------
1, User1

property (id, name)
-------------------
10, bicycle
11, car

label (id, name)
----------------
100, old
101, new

user_property_label (user_id, property_id, label_id)
----------------------------------------------------
1, 10, 100
1, 10, 101
1, 11, 100

The user_id foreign key is redundant information because user_id:1 will always be paired to label_id:100. Hence, the data can be further normalized by using the 2 foreign key option. And will look like this instead:

user (id, name)
---------------
1, User1

property (id, name)
-------------------
10, bicycle
11, car

label (id, name, user_id)
-------------------------
100, old, 1
101, new, 1

property_label (property_id, label_id)
--------------------------------------
10, 100
10, 101
11, 100
1

It's hard to answer that question without knowing what you are trying to model and the queries that are relevant to your problem domain. I recommend that you trace out some queries and see how that design works. Absent any other information, I would tend towards the 3 column option because the interpretation of a label depends on the property and you probably will do the query "give me all the labels associated with a user."

I think the most important question you should ask yourself are all the labels unrelated. If you are storing user preferences for an application, I can see the utility of your data model. On the other hand, if you want to store postal addresses, email address, access permissions, etc, then a different design is appropriate. Generic designs like this often shift data management and manipulation out of the DBMS into your application. Tables are cheap and I would recommend having tables of related facts/attributes (e.g. POSTAL_ADDRESS).

Revised answer based on edits:

First, never store an age in a database. Age depends on the current date. You should store a date and calculate the age (you can use a virtual column to do that).

Second, I suggest you identify the types of things you want to store in the database. Bicycles and car are types of vehicles. I would recommend a VEHICLE table that contains the facts (attributes, property). You can then store properties relevant to bicycles in a BICYCLE_ATTRIBUTE (or whatever name you prefer) table. Do this for all the types of data you want to store and try to make groups that related. Postal address might be a group and education might be another.

You should try to avoid a completely generic database because you will end up creating a DBMS in your application and the underlying DBMS has been relegated to a fancy file system.

  • Thanks Jim for reminding me that the queries can be helpful in determining the schema. I've added some queries and more information about the data that can hopefully better help decide one option over the other. – Zhao Li Oct 17 '20 at 15:32
  • I would like to better understand what you mean by rather or not the labels are "unrelated". Hopefully, the data examples can answer whether the labels are unrelated or not. I want to better understand how related or unrelated labels would affect the schema/queries. – Zhao Li Oct 17 '20 at 15:38
  • I hope the clarifications in the data and queries can help determine whether or not the design is appropriately abstract for the given dataset. If there is a less generic design that takes advantage of the cheap tables and make it easier for the application, then please let me know. I would really appreciate another option that makes more sense than the 2 options presented. – Zhao Li Oct 17 '20 at 15:38
  • Thank you again for your time in helping me out on this. I very much appreciate your thoughts and feedback. :) – Zhao Li Oct 17 '20 at 15:38

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.