Reputation: 21

Tricky database design

I need to design a database to store user values : for each user, there is a specific set of columns.

For instance, Jon wants to store values in a table with 2 columns : name, age.

And Paul wants to store values in a 3 columns table : fruit, color, weight.

At this point, I have 2 options.

Option 1 - Store data as text values

I would have a first table 'profiles' with the users' preferences :

+----+---------+--------+-------------+
| id | user_id | label  | type        |
+----+---------+--------+-------------+
|  1 |       1 | name   | VARCHAR(50) |
|  2 |       1 | age    | INT         |
|  3 |       2 | fruit  | VARCHAR(50) |
|  4 |       2 | color  | VARCHAR(50) |
|  5 |       2 | weight | DOUBLE      |
+----+---------+--------+-------------+

And then store the datas as text in another table :

+----+------------+--------+
| id | id_profile | value  |
+----+------------+--------+
|  1 |          1 | Aron   |
|  2 |          2 | 17     |
|  3 |          1 | Vince  |
|  4 |          2 | 27     |
|  5 |          1 | Elena  |
|  6 |          2 | 78     |
|  7 |          3 | Banana |
|  8 |          4 | Yellow |
|  9 |          5 | 124.8  |
+----+------------+--------+

After that, I would programatically create and populate a clean table.

Option 2 - One column per type

On this option, I would have a first table 'profiles2' like that :

+----+---------+--------+------+
| id | user_id | label  | type |
+----+---------+--------+------+
|  1 |       1 | name   |    3 |
|  2 |       1 | age    |    1 |
|  3 |       2 | fruit  |    3 |
|  4 |       2 | color  |    3 |
|  5 |       2 | weight |    2 |
+----+---------+--------+------+

with the type corresponding of a set of type : 1=INT , 2=DOUBLE , 3=VARCHAR(50)

And a data table like that :

+----+-------------+-----------+--------------+---------------+
| id | id_profile2 | int_value | double_value | varchar_value |
+----+-------------+-----------+--------------+---------------+
|  1 |           1 |      NULL |         NULL | Aron          |
|  2 |           2 |        17 |         NULL | NULL          |
|  3 |           1 |      NULL |         NULL | Vince         |
|  4 |           2 |        27 |         NULL | NULL          |
|  5 |           1 |      NULL |         NULL | Elena         |
|  6 |           2 |        78 |         NULL | NULL          |
|  7 |           3 |      NULL |         NULL | Banana        |
|  8 |           4 |      NULL |         NULL | Yellow        |
|  9 |           5 |      NULL |        124.8 | NULL          |
+----+-------------+-----------+--------------+---------------+

Here I have cleaner tables, but still a programmatic trick to implement to get everything in order.

The questions

Have anyone ever face this situation ?

What do you think of my 2 options ?

Is there a better solution, less tricky ?

Tx a lot!

Edit Hi again,

My model had a bug : impossible to retrieve a "line" of information; i.e. the informations in the "values" table are not sortables.

After some wanredings around the EAV model, it showed not suitable because it's not designed to store datas, but specific infos.

Then I ended with this model : Firt table 'labels' :

+----+------------+------+----------+
| id | profile_id | name | datatype |
+----+------------+------+----------+
|  1 |          1 | 1    | Nom      |
|  2 |          1 | 1    | Age      |
|  3 |          2 | 2    | Fruit    |
|  4 |          2 | 2    | Couleur  |
|  5 |          2 | 2    | Poids    |
+----+------------+------+----------+

Then a very simple 'nodes' talbe, just to keep track of the lines of infos :

+----+------------+
| id | profile_id |
+----+------------+
|  1 |          1 |
|  2 |          1 |
|  3 |          2 |
|  4 |          2 |
+----+------------+

and a set of tables corresponding to different datatypes :

+----+---------+----------+--------+
| id | node_id | label_id | value  |
+----+---------+----------+--------+
|  1 |       1 |        1 | John   |
|  2 |       2 |        1 | Doe    |
|  3 |       3 |        3 | Orange |
|  4 |       3 |        4 | Orange |
|  5 |       4 |        3 | Banane |
|  6 |       4 |        4 | Jaune  |
+----+---------+----------+--------+

With this model, queries are ok. Data input is a bit tricky but I will manage with a clean code.

Cheers

Upvotes: 2

Answers (3)

Daniel Williams

Reputation: 9304

Why not just have a user table with name and ID, the a userValues table that has key value pairs? that was John can have key "fruit" and value "mango, and another key "tires" and value "goodyear". Bob can have key "coin" and value "penny" and key "age" and value "42". Anyone can have any value they like and you have maximum flexibility. Speed won't be great, and you'll have to cast string to values, but it's always a tradeoff.

Cheers, Daniel

Upvotes: 0