I won't purport to tell you the 'correct' way to do it but I will tell you
how I would do it.
When I have information (marital status) I create a 'domain table'. I have 2
rules for most of my domain tables.
1: they end in the word Type. (maritalStatusType)
2: they have 3 fields:
The PK (maritalStatusTypeID)
description varChar(100)
endDate date
So in this case
maritalStatusTypeID description endDate
1 Married null
2 Single null
3 Divorced null
4 Widowed null
5 Shacked Up 2002-06-03
Now, in your members table you store the maritalStatusTypeID as a FK.
then a simply
select member.*,
maritalStatusType.description
from member left join maritalStatusType on member.maritalStatusTypeID =
maritalStatusType.maritalStatusTypeID
If you use the domain table to populate a select box (which I do) then the
endDate provides a mechanism whereby you can stop if from appearing in the
select box without deleting the record. (because, as we all know, deleting a
record from a domain table would leave orphaned FKs in the members table and
orphans are bad mojo.)
Anyhow, this has worked for me in the past.
=C=
*
* Cal Evans
* Journeyman Programmer
* Techno-Mage
* http://www.calevans.com
*
-----Original Message-----
From: Daren Cotter [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 04, 2002 6:38 PM
To: [EMAIL PROTECTED]
Subject: RE: Normalization question
I have a question about the setup of the tables in my database.
In my members table, I store a lot of info about demographics, such as
marital status, income, etc. The way I'm doing this is the enum type. Is
it better to use this, or would it be better to create separate tables
for each demographic I want to store? Advantages of this would be no
additional programming required when I want to list the # of members who
have selected each demographic...plus I could add new items any time I
wanted (i.e. I want to add "widowed" to the list of marital statuses).
What is the "correct" way to do this?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 04, 2002 6:24 PM
To: Daren Cotter
Subject: Re: Normalization question
Your message cannot be posted because it appears to be either spam or
simply off topic to our filter. To bypass the filter you must include
one of the following words in your message:
sql,query
If you just reply to this message, and include the entire text of it in
the reply, your reply will go through. However, you should first review
the text of the message to make sure it has something to do with MySQL.
Just typing the word MySQL once will be sufficient, for example.
You have written the following:
I have a question about the setup of the tables in my database.
In my members table, I store a lot of info about demographics, such as
marital status, income, etc. The way I'm doing this is the enum type. Is
it better to use this, or would it be better to create separate tables
for each demographic I want to store? Advantages of this would be no
additional programming required when I want to list the # of members who
have selected each demographic...plus I could add new items any time I
wanted (i.e. I want to add "widowed" to the list of marital statuses).
What is the "correct" way to do this?
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
