[Novalug] Database advice

William Sutton william@trilug.org
Mon Mar 15 15:23:24 EDT 2010


As opposed to, say, giving each dog record a mother_id and a father_id 
that point back to other dog records?  I think tracking litter as a 
specific join table is probably overkill.  You could get specific litters 
where the father_id, mother_id, and birth date are the same...

William Sutton

On Mon, 15 Mar 2010, Maxwell Spangler wrote:

> 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
>
> _______________________________________________
> Novalug mailing list
> Novalug@calypso.tux.org
> http://calypso.tux.org/mailman/listinfo/novalug
>



More information about the Novalug mailing list