[KLUG Programming] SQL questions

[bill]

On Mon, 2004-07-12 at 10:46, Rusty Yonkers wrote:
> I have a couple of newbie questions.  
> 
> First, I notice in my books that the SQL commands are in caps and the
> field names and such are all lower case but it works if I put
> everything lower case.  Are the commands made uppercase simply for
> readability?

Yup, and it's a good practice.

> 
> Second, in the solution that I am putting together I need to put
> marital status.  One of the possible choices will be divorced.  If
> the person is divorced, I need to track how long they have been
> divorced.  I want to capture date of divorce.  Should I make a
> separate table that will have the client_id field and then a
> divorce_date field?  This seems to be better normalization since only
> those that are divorced would even have a divorce date.  

Actually, you would go to a separate table when there could be multiple
divorces.  If only one divorce were ever recorded (like the latest, for
example), you could still keep that info in the original table and still
be normalized.  Some fields could be empty or null (if they have no
divorce) and things would still work fine.

> I am
> assuming that a record would only get created in the divorce_date
> table if a person enters an  actual date even though there is the
> field on the screen.  I would program it that the field on the screen
> would not even be available for data entry unless marital status was
> set to divorced or remarried.  I suppose I should also have a field
> for divorce_number for people that do the Elizabeth Taylor thing. 

Here's where you should think about a separate table.  If you really
just want to have a field for number of divorces and the date of the
latest, well, you could still have just one table.  But, more likely
you'll like to have records of each divorce.

So, main table would have 

personID, name, address, etc.

Related table would have

divorceID, personID, date, etc.

The tables could be joined on personID.  Normally, you'd just display
the main table data.  When looking to display divorce info, you'd just
run a search for all records in the related table with that personID. 
If none, no divorce, if one or more, you would list them.  

You could also find out whether a person has any divorce with a simple
query:

mysql_query("select * from divorce_table where personID=27"); // for
example

if (mysql_num_rows($result)) $divorce=TRUE;

kind regards,

bill