Simple Database Design Question

Question

Hi, I am developing web application that powered php and mysql. I have table named users, in this table i store some information such as username, first and lastname, telephone number etc.. In my application user can enter some OPTIONAL information such as e-mail newsletter options, company name(if works..) or web site url. All of these information(app have about 20 optional information type) is OPTIONAL. In this situation which kind of database design will be true ?

May be i can store all extra information in array and use serialize and save to database then while reading data i should use unserialize but this way have some cons. I am waitting ideas, thank you.

Answer 1

If you serialize the extra data into one column using php's serialize, you'll never be able to query that data. IE if you want to query for a user with a website like "http://www.foo.com", you cannot do this because that data is serialized.

I generally don't like storing serialized data in my database unless there is no way around it.

Answer 2

I can't see any benefits of not having all the data you want to capture as fields in the database.

If you serialise the data in some way you won't be able to query on it, it will be always more work to get the values out and it will make it much more difficult for someone else to understand your design.

If you have separate columns it will be much easy to find the data you want and if you have an editor with something like Intellisense it will auto-complete on the column names.

What benefits do you expect to find from serialising the data? Most databases are pretty clever about how they store data so you're not going to save any disk space, and chances are that won't ever be an issue anyway.

Answer 3

I'd just go for one table - user, with OPTIONAL fields having default value NULL.

Answer 4

Adding those optional fields as multiple table fields or a single "OPTIONAL" one depends on the application in cause, but i would say that if you are sure that none of those fields will be used in any way for SQL queries, then is should be ok.

One thing to note, i would NOT use serialize() but instead:

$json = json_encode($data);

and

$data = json_decode($json, true)

unlike serialize(), json could be unserialized elsewhere (javascript/browser), is binary-safe, and it's also human-readable.

Answer 5

I'm sure there are many ways to do this as a single / generic entity in you schema design.

Serialization , key value lookup table etc etc ...

But with only 20 columns I would question if you should. Let your tools and the db do the work and keep them as columns. Doing it in any other way may be something you will regret later when you want to report or extract this data in the future.

Answer 6

Why would you do that ?

There is no benefit : you can't query it, it pretty obscure to any other developper that doesn't know it ? etc.

Best to do is the have 1 fields for each optionnal information, and set the "Nullable". If user input nothing, then the row contains DBNull.

Answer 7

Serializing prevents you from querying one particular data. Definitely you may want to know who and all opted for newsletter. Then you should store each information in separate column.

You may be tempted to use NULL values for optional columns. But performance wise, NULL values put a stress on the server in terms of indexing and sorting. So it is recommended to have empty string or zero as default values for columns. And from PHP, you can check the state using empty() method.

Answer 8

Add an extra table OptionalInformation with three columns:

• FK_UserID
• OptionalFieldName
• Value

The combination of the three is your primary key for this table. This is much easier to query than a serialized column.

It also makes it easier if you decide to store more information later. You won't have to add an extra column to your user's table, instead you'll add a row to the OptionalInformation table