Take the 2-minute tour ×

I am new to modeling and creating these relational databases, so any recommended reading would be great after reading what I am trying to do.

I am creating a table for contacts. There is currently these tables: vendors, primarycontacts

the columns in primary contacts look like this:

table contacts: id, name, vendor, phone

Now sometimes, a contact has multiple phones (Cell, office, home, skype, etc..) so I was thinking of making another table "Phone" with columns: ID, Name, and Phone. Also, some contacts may share the same phone number.

This would be a many to many relationship, am I right. Many contacts sharing phone numbers that all work for the same vendors or different vendors. W

Would it be good to do this (Seperate phone numbers table), instead of having Ph1, Ph2, Ph3 column in contacts? What if there are more numbers? And what if there is only one phone number? This would be wasted space. Should I only have ID and phone_number in the phones table, or also have ID, Phone_Number, and Phone_Name?

share|improve this question
add comment

4 Answers


Yes, you will want to create a separate "PhoneNumbers" table. You can then remove the "phone" field from "Contacts" all together.

Your "PhoneNumbers" table is going to have two fields: contacts_id and phone_number (as a concatenated key). This will enable your design to be flexible enough to allow contacts to have multiple phone numbers.

share|improve this answer
Are you saying that the Primarycontacts_id is going to be a PK? Or should it have three fields: id(pk), primary_contacts_id, phone_number? –  user1185860 Feb 2 '12 at 18:51
The PK will be a concatenated key based-on contacts_id and phone_number. If you do that, you shouldn't need a separate id field at all. –  BryceAtNetwork23 Feb 2 '12 at 18:54
Phone_type, home, work etc might be necessary as well based on the OPs question. –  Ben Mar 17 '12 at 21:54
add comment

It depends on your project but the idea of many to many does not make sense when it is private info. Think about what happens when someone edits a phone number. Now all other users using that one have changed info, even if they don't want to and are not aware of.

Try something like:

table phones: id, contact_id, phone
table contacts id, user_id, firstname, lastname etc.
share|improve this answer
Ah, thanks, I should have thought of that, excellent point! One to Many (Contacts to phone numbers) sounds more appropriate, no? –  user1185860 Feb 2 '12 at 18:46
Yes, I would go that way. Or you should be building some kind of Yellow Pages ;) –  Luc Franken Feb 2 '12 at 18:50
add comment

You can do a many to many relationship but as Bryce says you need to be aware that changes to phone number may affect more than one contact. If you can guarantee you are only doing inserts and no updates to the phone number table you would be fine. You would do inserts, updates, and deletes to the mapping table to manage these relationships.

You would have three tables. Contact, Phone_Number, and Contact_Phone_Number. Contact_Phone_Number would just have the contact id and phone number id. This would give you the many to many relationship.

To use this for example, if a user changes their number remove the mapping between the number and the contact in Contact_Phone_Number and set up a new mapping. If the number is new you would insert into Phone_Number. If it already exists just map to the existing phone number record.

The benefit of this is it makes it much easier to query which contacts share phone numbers since there is only one record per logical phone number. The decision depends on how you want to use the data.

share|improve this answer
Three tables was my first thought, too. But all he's really storing is the phone number itself. And since it makes a good natural key, the bridge table (Contact_Phone_Number) is really all he needs here. –  BryceAtNetwork23 Feb 2 '12 at 19:08
Valid point Bryce. –  nolt2232 Feb 2 '12 at 19:20
So are you saying having this is better? : table: contact: ID, fname, lname, email, etc... table: contact_phone: contact_id, contact_phone –  user1185860 Feb 2 '12 at 20:01
add comment

it may be an old post, but if someone reads it...

you have contacts, vendors and phone. Contacts can work at the same vendor and maybe some contacts share the same phone number.

What I would do, is simply create 3 tables:

contacts: id, name, vendor_id, phone_id vendors: id, vendor phone: id, phone

The relations between those tables are quite simple actually...

share|improve this answer
add comment

Your Answer


