Database design for multiple user attributes

I am building an app with Laravel, and I have users which also have a profile.

I am unsure about the best way to set up the database for the user attributes.

For example:

user_ud  |   name  |  gender  |  haircolor
   1         josh       1           3

This quick example shows what my table for profiles currently looks like, as the vales are filled in from select boxes.

Is this the best way to map these attributes to values like this?

 id  |  gender
  1      male
  2      female

 id  | hairColor
  1      blonde
  2      brown
  3      black

and so on creating a new table for each attribute type? or is there a better way of doing this?

You could look at using Entity-Attribute-Value design.

This consists of a table with three columns, one for the user id, one for the type of attribute, and one for the attribute value.

With this design, you can have an unlimited number of attributes without needing to implement new columns for new attributes.

Many architects will say this design cannot scale well, and they’d mostly be right; however if implemented correctly without going overboard on the number of attributes, and with proper indexing and efficient column types, it can work well.

For instance:

+---------+------------+
| USER_ID |  USER_NAME |
+---------+------------+
| 1       | You        |
| 2       | Me         |
| 3       | Them       |
+---------+------------+

+--------------+-----------------+
| ATTRIBUTE_ID |  ATTRIBUTE_NAME |
+--------------+-----------------+
| 1            | Eye Color       |
| 2            | Hair Color      |
| 3            | Number of Legs  |
+--------------+-----------------+

+---------+--------------+--------+
| USER_ID | ATTRIBUTE_ID |  VALUE |
+---------+--------------+--------+
| 1       | 1            | Brown  |
| 1       | 2            | Blue   |
| 1       | 3            | 2      |
| 2       | 1            | Blonde |
| 2       | 2            | Green  |
| 2       | 3            | 27     |
| 3       | 1            | Black  |
| 3       | 2            | Black  |
| 3       | 3            | 42     |
+---------+--------------+--------+

If you have a fixed set of user attributes then your current design is OK. If you need to have custom attributes (i.e. users can add their own attributes) then you’ll have to go with the solution Hannah Vernon proposed. To store each attribute in it’s own table seems like the worst choice for me, as it’s no more flexible than having all the attributes as columns in a single table, but is much harder to query (requires a join for each attribute) and uses a lot more space on disk – which could also lead to serious performance degradation.