The design question is many, many

Question

Say that you have the following many-to-many relationship:

user ----- id first_name last_name user_prefs ---------------- id preference_name user2user_prefs ------------- id user_id user_pref_id 

But say that you have the user's preference for the "home page" and you need to save the actual URL of the main page somewhere. Where will it go?

I cannot put the value in user_prefs, because then the same value will be applied to everyone who has this mapping.

I can put it in user2user_prefs like this:

 user2user_prefs ------------- id user_id user_pref_id value 

But is this the best way to normalize? Something is not quite right about how this is done (firstly, I can’t use ENUM for the new “value” because it will have to contain all the values ​​for all the settings). Any thoughts?

Thanks! Stabby l

Answer 1

You should look at functional dependencies. The value does not depend on the user, it does not depend on userpref, but it depends on both. That would mean that you need to put it in the tab2 of user2user_prefs as you suggested.

If you need to use an enumeration, then the value attribute can look up values ​​in another table.

Answer 2

I can’t put the value in user_prefs because then the same value applies to everyone who has the mapping.

You normalize too far. The home page will be in the order of user_prefs.

In fact, if I were you, I would use only one table:

 user ----- id first_name last_name homepage <other settings> 

External key relationships, especially many-to-many relationships, are of great value in complexity. And the software developer’s job is to keep complexity under control.

Answer 3

It seems to me that a certain type of value does not actually belong to many, many relationships. If a specific property value is specific to each user, then it looks like it should be a separate table. He "feels" how there would be a 1-lot relationship from user to another table that has preferences unique to each user.

Edit: for a table from 1 to many:

 user_specific_prefs ------ id user_id pref_name (or possibly pref_id that indicates the type) pref_value (store www.myhome.com for example) 

User_id is only a foreign key for a specific user. This is essentially (in a logical sense) adding extra columns to the user table. But, as Andomar points out, this adds to the complexity. But if you have so many preferences, that might be good. On the other hand, I saw tables with hundreds of columns. I am not saying that this is good (and I did not create them), but they do their job and are easy to use.

Answer 4

You are very close.

[Edit: My previously considered brilliant answer was not so bright.]

Edited Tables:

 users ----- id first_name last_name preferences 

The preference field will store a serialized object or an array of specific user options. In PHP:

 if ( is_array( $options ) || is_object( $options ) ) serialize( $options ); 

Serialize Guide