sql database model for contact details

Question

I need a db to store, i.e. user records. Regular stuff: name, e-mail, address, phone, fax and so on. The problem is, in this case there can be more than one phone number per user. And more than one e-mail. Even more than one address. And a lot more of more-than-one stuff.

One approach is to store everything in one table, e.g. serialized phones array in one phones column. Or comma separated phones in one phones column. But I really dont like this way, I'd rather do overcomplicated database to make programming logic simpler than the other way round.

Another one is individual table for phones, individual table for addresses and so on. Columns: id, customer_id, phone. customer_id references customers.*id* Now this seems like a real overkill, having around 10 tables just for storing contact details.

And yet another idea I came up is one additional table for contacts with columns like id, customer_id ( <-foreign key), key, value. Where key can be "phone" and value "+123 3435454", or key "e-mail" and value... you got the idea. So far I like this one best.

What would you suggest? What would be downsides of #3 method?

db I'm going to use is postgresql, but it doesnt really matter.

Answer 1

Downsides:

1. That violates first normal form, as you'll have many values for one field
2. As you said, that would be a nightmare to maintain

3. Seems bets option but could be normalized by using a domain table for that "key" value

   • Customer (Id, Name)
   • AdditionalData( Id, Name ) (phone, address, etc)
   • CustomerAdditionalData(Id, CustomerId, AdditionalDataId, Value)

Answer 2

One table row per entity is correct. So one for emails, one for phone numbers etc is correct. it's not overkill: it's normalised database design.

Your option 3 can be done, but, say, what if you want to enforce a certain pattern for phone numbers and email addresses?

Answer 3

Some purists would suggest no 3 to be the way to go. In fact using that approach you could theoretically build a single table database!

However like gbn mentioned, this would cause problems with specific formats, and you would have to enforce data lengths etc on the client only.

I would go with your 2nd suggestion and use the approach of having different tables, with an address type, phone number type etc similar to that shown below.

id number addrss_type varchar (home/contact/mail etc) addrs_line1 varchar addrs_line2 varchar etc, etc

Answer 4

Some may say that method #3 is the best. Others will say that it is basically one of the most common anti-patterns in databases - namely EAV, which gets a lot of hate.

As for me - I know too little about your application to suggest solution. Generally - method #2 gives you the most functionality.

There is also method #4, and it's variant - method #5:

4 - use arrays of values - i.e. phone column, instead of being of TEXT database, is TEXT[], and then you can store many phones in it.

5 - since you're on PostgreSQL - use it. There is pretty cool hstore datatype in contrib, which you can use instead of arrays to add some semantics (like type of phone).