views:

155

answers:

1

Say I want users to pick one or more contact methods (email, phone, fax, other, etc). And if they choose other, then they can enter a single contact method of their own. What's the best way to store this in a database? I see three possibilities:

  1. Use a set datatype column, plus a single "other_contact" varchar column to store the optional user-entered value.
  2. Use many-to-many, and a user-entered column. So there would be a user table, a contact_method table, and a user_contact_method table that connects the two. Plus a user.other_contact varchar column to store the optional user-entered value.
  3. Just use many-to-many. Same setup as 2 but let users add entries to the contact_method table. But this means I'll have to add a column to keep track of "system" values (these cannot be changed or deleted by users, and only these show up in a dropdown). Plus I'll have to add extra logic to allow their user-entered value(s) to be changed.

Any comments on the above, or is there a better solution?

A: 

If you have one user and several contact method possibilities, it's most probably a one-to-many relationship. (I would avoid many-to-many wherever you can, because of the additional complexity and the slow down of querying such models.)

users_table 
id INTEGER
name VARCHAR 
etc.

contacts_table
user_id INTEGER -- foreign key on user.id
contact_method ENUM('email', 'phone', 'mail', 'other')
contact_address VARCHAR
etc.
Beffa
I see where you're going, and I like removing the complexity of a many-to-many solution. The only issue is if I had another table that also needs contact methods. I would have to create a similar table for each, right?
M P
you could create a table for each, if you need high performance select-statements and therefore foreign_keys. Another solution would be to rename "user_id" to "foreign_id" and add an additional column "table". (The second solution is somehow polymorphic object oriented.) For an users contacts_table entry, the foreign_id would store the [users id] and in the "table" field you would have the string "users_table".
Beffa

related questions