SQL Structure of DB table with different types of columns
- by Dmitry Dvornikov
I have a problem with the optimization of the structure of the database. I'll try to explain it exactly.
I create a project, where we can add different values??, but this values must have different types of the columns in the database (eg, int, double , varchar). What is the best way to store the different types of values ??in the database. In the project I'm using Propel 1.6. The point is availability to add value with 'int', 'varchar' and other columns types, to search the table was efficient.
In total, I have two ideas. The first is to create a table of "value", which will have columns: "id ", "value_int", "value_double", "value_varchar", etc - with the corresponding column types. Depending on the type of values??, records will be saved with the value in the appropriate column (the rest will be NULL).
The second solution is to create separate tables such as "value_int", "value_varchar" etc. There would be columns: "id", "value", which correspond to the relevant types of "value" (ie, such as int, varchar, etc).
I must admit that I do not believe any of the above solutions, originally I was thinking about one table "value", where the column would be a "text" type - but this solution would probably be even worse.
I would like to know your opinion on this topic, maybe something else would be better. Thanks in advance.
EDIT:
For example :
We have three tables:
USER: [table of users]
* id
* name
FIELD: [table of profile fields - where the column 'type' is the type of field, eg int or varchar)
* id
* type
* name
VALUE :
* id
* User_id - ( FK user.id )
* Field_id - ( FK field.id )
* value
So we have in each row an user in USER table, and the profile is stored in the VALUE table. Bit each profile field may have a different type (column 'type' in the FIELD table), and based on that I would want this value to add to the appropriate column of the appropriate type.
