Database design for changing number of fields

Question

Google Contacts lets you to enter more than one phone number, or email address for a contact. You can add as many as you want. I don't know if there is a limit, other than practical list size. To build this type of structure I normally create a table with userid, key, and value fields, so a user can keep an unlimited number of records.

I've read that key-value table design performance is low and they do not suggest it. I have not used this type of structure for more than 50-100 users. At the moment, I am working on a big project and I need to keep information about accounts.

Without Key-Value pairs, I will define lots of fields to keep information of each account. I need a new kind of field later, I will add it for all account records.

I need ideas / sources / documents about how big businesses are building this type of structure.

For now, I expect to reach 2000-3000 customers in next 3 years. I normally use MySQL and PHP.

P.S. Each account will have at least 5 different kinds of information to start. In time, this will increase according to requirements.

Answer 1

KEY-VALUE NO!!!

A table for phone numbers -- sure. It would have userid, phone_num, and (if you like) a phone type, such as ENUM('fax', 'home', ...). Then JOIN to the main table.

To keep unlimited, unsearchable data, have a column with a bunch of key-value stuff. I like to do it in JSON, then compress it (in the app), and store it into a BLOB or MEDIUMBLOB. That makes it easily accessible by the app, reasonable compact, and quite open-ended.

In the table, have only columns that you need to search on; put the rest into the extra JSON column.

More discussion: http://forums.mysql.com/read.php?125,428546,428769#msg-428769 http://forums.mysql.com/read.php?125,402095,402218#msg-402218

Another approach is MariaDB's "dynamic columns". This even lets you index randomly added 'columns'.

2000-3000 customers -- Yawn.

Answer 2

Few suggestions:

1 - Don't use dynamic fields unless you really have to because they add complexity to your system. In your case, you don't have to.

2 - Don't add redundant static fields like this:

Contact: ContactID,...,Phone-1, Phone-2,...,Phone-20, ...

This is against normalization and is justified only in few cases (e.g. where the columns are limited in number and rarely searched directly). If you do this you can never find a Contact given a phone number in a decent way and you will have lots of Nulls to deal with also, you could have duplicate phone numbers (if you are not careful in your coding).

3 - If you have a structure like: Contact: ContactID, ...

ContactMethod: ContactMethodID, Value, ContactID_FK

where a Contact could have 0,1, more ContactMethods, that is a perfectly normal 1-M relationship that will perform very very well if you have millions of rows provided that you have an index properly defined on ContactID_FK. On your client side, you have to be careful what you want to show and how large is the data your are communicating with the server. This is a different issue and I don't think it would affect your case.

The option in (3) is probably the best, it has been working since the 80s very well!

Answer 3

You should model your entities as entities. A user is a user table. A phone number is a phone number table†, and a user has a relationship with a phone number over a period of time. This is the correct way to do it.

User
id
name

Phone
id
value

UserPhone
userId
phoneId
from_date
to_date (nullable)

† consider a more abstract Address table with a type discriminator