[Novalug] Database advice

Bonnie Dalzell bdalzell@qis.net
Mon Mar 15 15:54:29 EDT 2010


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.

Actually now that we do DNA parent testing on litters it is possible to 
have pups with different fathers born on the same day from the same 
mother. In fact in a rare breed such as I own there are 
advantages of having dual sired litters in terms of preserving genetic 
diiversity in the breed without having to produce more pups than you 
can find homes for or having to cull the pups that you are not using in 
a breeding program to produce the next generation. We have had a number 
of registerable dual sired litters now. Each sire's offspring is 
registered as a separate litter despite the common mother. Obviously when 
a sire is popular you cannot id all his litters just by birthdate. So 
both parents are needed.

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

Yes indeed. also many dogs have multiple owners or breeders because people 
form co-operative groups to breed a litter or to own an exceptional show 
dog or an exceptional athletic performance dog. So i need an owners table 
with dog id and then multiple owner id's.

The thing I am not sure about is that I think the table has to have a 
predetermined number of columns - it is rare for more than 4 people to 
own the dog or be listed as breeders. I do not think the table can be 
open ended.

The alternative is to make the owner or breeder list one entry with the 
individual people_id numbers separated by something like the pipe symbol 
and then use a program module to process the contents of the cell to list 
the individual people by name. This is what i decided to do when I was 
using a flat file for the dog db. I had a separate people flat file with 
each person having a different id number.

It does have the disadvantage of making corrections difficult due to 
having to update all those cells.

The real world really does get complicated.

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                        Bonnie Dalzell, MA
mail:5100 Hydes Rd PO Box 60, Hydes,MD,USA 21082-0060|EMAIL:bdalzell@qis.net
Freelance anatomist, vertebrate paleontologist, writer, illustrator, dog
breeder, computer nerd & iconoclast... Borzoi info at www.borzois.com.
HOME www.batw.net    ART bdalzellart.batw.net  BUSINESS www.boardingatwedge.com




More information about the Novalug mailing list