Many-to-many database design question

Question

Say you have the following many-to-many table relation:

user
-----
id
first_name
last_name

user_prefs
----------------
id
preference_name

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

But say you have the user preference of "homepage" and need somewhere to store the actual homepage url. Where would that go?

I can't put a value in user_prefs because then the same value would apply to everyone who has that mapping.

I CAN put it in user2user_prefs like so:

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

But is this the best way in terms of normalization? Something doesn't feel quite right about the way im doing it (for one thing, i can't use an ENUM on the new "value" because it would have to contain all values for all preferences). Any thoughts?

Thanks! Stabby L

Answer 1

You have to look at the functional dependencies. The value does not depend on the user, it does not depend on the userpref but it does depend on both. It would imply that you need to place it in the user2user_prefs tabvle as you suggested.

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

Answer 2

I can't put a value in user_prefs because then the same value would apply to everyone who has that mapping.

You're normalizing too far. The homepage would be fine in user_prefs.

In fact, if I were you, I'd use just one table:

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

Foreign key relations, especially many-to-many relations, have a large cost in complexity. And the job of the software developer is to keep complexity under control.

Answer 3

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

Edit: For the 1 to many table:

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

The user_id is just the foreign key back to the specific user. This is essentially (in a logical sense) adding additional columns to the user table. But as Andomar points out, it does add complexity. But if you have a huge number of preferences like that, it might be good. On the other hand, I have seen tables with hundreds of columns. I'm not saying that is good (and I didn't create them), but they get the job done and are easy to use.

Answer 4

You're very close.

[Edit: My previously thought to be brilliant answer was not so brilliant.]

Edited tables:

users
-----
id
first_name
last_name
preferences

The preferences field would hold a serialized object or array of the specific users options. In PHP:

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

Serialize Manual