[Novalug] Database advice

Maxwell Spangler maxlists@maxwellspangler.com
Mon Mar 15 14:57:34 EDT 2010


On Mon, 2010-03-15 at 11:37 -0400, Bonnie Dalzell wrote:
> each dog has a unique entry into the db
> 
> one of the aspects of the dog's record is that, when they are known, it 
> has a mother and a father
> 
> the mother and father of course also have their own records in the db
> 
> how do you link the offspring's record to the parent's records?
> 
> do you need to do a separate table of "litters" where the key is a litter 
> id and the 3 information cells are father record number, mother record 
> number and date of birth or can you make cells (mother, father) in the dog 
> record that refers to the parents record id in the dog records table?

I got stuck on this last night and I'd like to see what others think.

In my opinion, if you make a DOG record to describe a dog, then all the
fields in that record should be focused on describing the dog.  That's a
redundant statement that reinforces the point: try your best only to put
fields in the dog record that describe the dog and no other data.  This
helps normalization because all these facts about the dog will be stored
once and only once in the database: right next to the dog being
described and they won't be repeated:

do:

dog  <- lab, black, tall, 100#, male, born 01/01/08

don't:

dog <- lab, black, owner's:mary, owner:club member, owner: pays with CC

In that example, the owner information should be in an owner record
where the fields describe the owner more in detail.  Otherwise, if the
owner has two dogs, you're repeating information in two places (the two
dog records) and this can become a big problem later.

I would, therefore, make a 'litters' record that describes a litter
event or relationship.  Give it a unique litter ID, then describe
anything related to it and link it to each dog and the mother and
father.

I would love to see some others give examples of SQL code to show
linking a litter record with a single mother from the dog table,a single
father from the dog table and multiple children.  There are a few ways
to do this I imagine and I'm eager to learn what is available...

hth Bonnie,

-- 
Maxwell Spangler




More information about the Novalug mailing list