2

I've got a database where I want to store user information and user_meta information.

The reason behind setting it up in this way was because the user_meta side may change over time and I would like to do this without disrupting the master user table.

If possible, I would like some advice on how to best set up this meta data table.

I can either set it as below:

+----+---------+----------+--------------------+
| id | user_id | key      | value              |
+----+---------+----------+--------------------+
| 1  | 1       | email    | test@testemail.com |
| 2  | 1       | name     | user name          |
| 3  | 1       | address  | test address       |
...

Or, I can set it as below:

+----+---------+--------------------+--------------------+--------------+
| id | user_id | email              | name               | address      |
+----+---------+--------------------+--------------------+--------------+
| 1  | 1       | test@testemail.com | user name          | test address |

Obviously, the top verison is more flexible, but the bottom version is space saving and perhaps more efficient, returning all the data as a single record.

Which is the best way to go about this?
Or, am I going about this completely wrong and there's another way I've not thought of?

Dan Hanly
  • 165
  • 5
  • 1
    It may help your research to know that your first example is known as EVA (http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model). It is often considered an anti-pattern from a relational perspective, but there are times whe it is the only viable approach. – Kramii Oct 19 '13 at 21:39

0 Answers0