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