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 use lookup tables for even small things like Gender which have only 2-3 values.
A:
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
Plaudit Design - Web Design
2010-10-13 17:58:41
I dont mean the type. I mean the values. Age is from 1 till say 120. All those values -> Are we storing those as text field data in database or should those all be a lookup table like Age_id, Age where each row is one number from 1 till 120 so we can FK reference it. I ask because I have a whole Analytic component to be designed out, so mapping data across tables and maintaining std values is very very critical across system. Like I have seen ex where Birth year has a lookup table also for all possible years. At the same time i have seen many who store it as date/text.
Karem
2010-10-13 19:17:53
@Karem I think my answer and his are essentially the same. For the fields you mentioned, you need to find data types that are appropriate to what you want to store. A date field can hold month, day, and year in one field, then later if you need just month, you can split it out using a database function. And age can be derived from date of birth, so storing it is redundant.
pheadbaq
2010-10-13 21:30:43
A:
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.
pheadbaq
2010-10-13 21:20:16