[KLUG Programming] SQL questions

[Adam Tauno Williams]

> > 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.

Yes.  Greatly improves readability.  Some SQL editors will also to
context-based coloring of text which is very nice, especially for making
syntax errors much more obvious.  DbVisualizer is my favorite.

> > 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.  

Thats a tough one since a person can have zero or more divorces.  I'd
create a second table, something like marital_status, of the struct
(client_id INT FK, 
 marriage_date DATE, 
 conclude_date DATE, 
 conclude_type SMALLINT 
   DEFAULT 0 
  CHECK ((conclude_type == 0) OR 
         (conclude_type == 1) OR 
         (conclude_type = 2))

with 0 indicating married, 1 indicating divorce and 2 indicating death
of spouse.  Then a client can have multiple serial marriages.  Finding
the status of the client currently is as easy as a MAX aggregate call
for the client_id,  put this in an OUTER join view and it should be
seemless for the application to use.

> > 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.

Yep.