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:
- Use a set datatype column, plus a single "other_contact" varchar column to store the optional user-entered value.
- 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.
- 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?