Simple Database Design Question
I am developing a web application that supports php and mysql. I have a table named users, in this table I store some information like username, first and last name, phone number, etc. In my application, the user can enter some ADDITIONAL information such as email distribution parameters, company name (if working ..) or website URL. All this data (the app has about 20 optional information types) are OPTIONAL. In this situation, which database design would be true?
Maybe I can store all the extra information in an array and use serialization and persistence in the database, then I have to use unserialize when reading the data, but that has some drawbacks. I'm looking forward to ideas, thanks.
source to share
Add an optional OptionalInformation table with three columns:
- FK_UserID
- OptionalFieldName
- Value
The combination of 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 save additional information later. You will not need to add an extra column to your custom table, instead you will add a row to the OptionalInformation table
source to share
If you serialize additional data into one column using php serialization, you can never query that data. IE, if you want to query a user with a website like " http://www.foo.com ", you cannot do that because this data is serialized.
I generally don't like storing serialized data in my database if there is no path to it.
source to share
Why would you do this?
There's no benefit: you can't ask for it, is it pretty obscure to any other developer who doesn't know it? and etc.
It is best to have 1 field for each optional information and set it to "Nullable". If the user doesn't enter anything, the string contains DBNull
.
source to share
I don't see any benefit from not having all the data that you want to capture as fields in the database.
If you serialize the data somehow, you won't be able to query it, it will always work harder to get the values, and it will make it difficult for someone to understand your design.
If you have individual columns, it will be very easy for you to find the data you want, and if you have an editor with something like Intellisense it will auto-populate with the column names.
What benefits do you expect from data serialization? Most databases are pretty smart about how they store data, so you are not going to save disk space, and most likely it will never be a problem.
source to share
Adding these optional fields as multiple table fields or one "OPTIONAL" depends on the application in the reason, but I would say that if you are sure that none of these fields will be used in any way for SQL queries then this should be fine.
One note, I would not use serialize (), but instead:
$json = json_encode($data);
and
$data = json_decode($json, true)
Unlike serialize (), json can be unserialized elsewhere (javascript / browser), is binary safe, and it is human accessible as well.
source to share
I'm sure there are many ways to do this as a single / shared object in schematic design.
Serialization, lookup table for key values, etc. etc.
But with only 20 columns, I would pose the question if needed. Let your tools and db do the work and store them as columns. Doing it any other way might be something you regret later when you want to report or retrieve this data in the future.
source to share
Serialization prevents you from querying individual data. Definitely, you might want to know who selected the newsletter and who. Then you have to store each information in a separate column.
You might be tempted to use NULL values for additional columns. But performance metrics, NULL put a load on the server in terms of indexing and sorting. Therefore, it is recommended to use an empty string or zero for columns. And from PHP, you can check the status with the empty () method.
source to share