[Novalug] Database advice

[William Sutton]

This thread is going to the dogs :-)

William Sutton

On Mon, 15 Mar 2010, John Holland wrote:

> I was tempted to weigh in, now I find it too tempting not to.
>
> This situation seems to me to need a DOGS table where each dog has a
> Father and Mother fields.
> Those will contain IDs to point to the appropriate dogs, or NULL or some
> signature value when there is no record to rely on.
>
> This is not as flexible as could be designed, but I think it would be
> straightforward and perform well. Biology imposes a certain structure to
> the data..........
>
> Since it sounds like Bonnie already has IDs for each dog, she should be
> able to use those. I would make the database autogenerate a primary key
> of its own though, it can just be ignored but it might be useful someday
> and costs nothing.
>
> Litters could be identified by birthday, without requiring an addtional
> table (mom + birthday =  unique litter) , or there could be an
> additional Litter table with links from the dogs to that as well. (a dog
> has a Litter ID attribute)
>  And probably links from the litter table to the mother & father dogs.
>
> When humans get involved, you could have owners and breeders who would
> complicate things; dogs could change hands or there could be multiple
> owners or breeders. These would require join tables, possibly with start
> and end times as part of the join attribute.
>
>
> Dog <-------    owned from 1/1/2005 to 1/01/2006  --------> owner Joe Smith
> <-------    owned from 1/1/2006 to present    ----------> owner Fred Jones
> <-------    owned from 1/1/2006 to present    ----------> co owner Joe Jones
> so the records for the middle part are like
>
> -------------------------------------------------------------
> DogId  |   start date  | end date  | ownerId
> --------------------------------------------------------------
>
> OK, I guess that's enough for me to throw out there..................
>
>
> John
>
>
> On 03/15/2010 02:57 PM, 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,
>>
>>
>
> _______________________________________________
> Novalug mailing list
> Novalug at calypso.tux.org
> http://calypso.tux.org/mailman/listinfo/novalug
>