patternsqlMinor
Database design for changing number of fields
Viewed 0 times
numberfieldsdesigndatabaseforchanging
Problem
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
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#23370, answer score: 4
Revisions (0)
No revisions yet.