Lookup tables for basic user input?

Data like Birth month, day and year, user's age, Gender/Sex, etc. Should these be stored as text or ID based in database? ID based means they will have lookup values. Usage is for example: User signup will record age, user profiles will have a seeking partner age, etc so age and other data can be used in multiple places. In backend there will be analytic which is pushing me to u开发者_开发知识库se lookup tables for even small things like Gender which have only 2-3 values.

You will want to reference the datatypes or database has avilable. For mysql: http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Do not use any of the fields you mentioned as the primary key. Either create an 'id' column or use the user's username.

Here are database for the fields:

• birthDate = date
• age = tinyint (Technically you don't need this since you can always determine it based on their birthDate and current date. It depends what you are doing)
• gender = enum

I wouldn't bother with lookup tables for the fields you mentioned. Birth month, day, year could all be encapsulated in a single date field (w/ date type, not text), and then split out with database functions if you need. Age is just a number, which is all your id field will be, so not much point in making a lookup for that unless you want to actually limit the age range, in which case you could use a check constraint instead of a lookup if you needed to limit to an actual range (age >= 1 and age <= 20 for instance). Gender/sex is the only one I might consider a lookup on, but since there are so few possible values, a check constraint should suffice.

Oh, and I don't know what analytic you're using, but any analysis software worth its salt can produce field domains (lists of unique values in a table's fields) on its own, especially for the fields you mentioned. If you're building your own analytic (not sure, based on your comment to another poster's solution), you can easily make queries to do what you're talking about.