[Novalug] Database advice
william at trilug.org
Mon Mar 15 15:39:47 EDT 2010
This thread is going to the dogs :-)
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..................
> 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:
>> dog<- lab, black, tall, 100#, male, born 01/01/08
>> 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
>> 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
More information about the Novalug