Database design for multiple user attributes

Question

i am building an app with Laravel, and i have users which also have a profile. I am unsure as the best way to go about setting up the database for the user attributes for example

user_ud  |   name  |  gender  |  haircolor
   1         josh       1           3

as you can see from this quick example this is what my table for profiles currently looks like, as the vales are filled in from select boxes.

my question is. is the bes way to then map these vales to a meaningfull value 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?

ceejayoz · Accepted Answer · 2015-04-19 21:32:41Z

2

I'd use an ENUM for these:

$table->enum('gender', ['male', 'female']);

answered Apr 19, 2015 at 21:32

ceejayoz

181k42 gold badges311 silver badges382 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Josh Kirkpatrick Over a year ago

just so i get this right, using this is like saying 1 = male, 2 = female and so on?

ceejayoz Over a year ago

No, you'd use the actual values male/female. No reason to complicate it with numeric IDs that don't correspond to anything meaningful - $user->gender = 'male' makes a lot more sense in the code than $user->gender = 1.

Ivan Batić Over a year ago

You can also use numerical keys for enum values if you wish.

Ivan Batić · Accepted Answer · 2015-04-20 23:03:58Z

0

Is hair color important as an entity? Do you insert colors by themselves and if nobody has red color, do you still need information about it's existence in the database?

With this kind of data, it's usually not the case and prone to over-normalization of the database, which results in additional queries and no benefits.

Storing the color as a string value is perfectly fine in this case. Enum is also ok for the gender. That's unless you are Facebook and need dozens of "gender" options for people who feel like their rights are violated if you don't include their own specific sexual perspective.

answered Apr 20, 2015 at 23:03

Ivan Batić

4762 silver badges8 bronze badges

Collectives™ on Stack Overflow

Database design for multiple user attributes

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related