[Novalug] Database advice

[John Holland]

That's pretty weird about the multi-father litters. Learn something 
every day.......


As far as fixed number of columns, you can always avoid that by having a 
many-to-many relationship.

For instance if a litter could have any number of fathers, you give the 
litter an ID (could be autogenerated), have a fathers table (which is 
unlimited) and have a "join table" which has in its two columns a litter 
ID and a father ID. This allows any number of connections between 
litter(s) and father(s).  To get the litters a father sired (even part 
of), you would

SELECT litters.ID, litters.littername
from litters, join_table
where litters.id = join_table.litter_id
and join_table.father_id = 123.

(to get the litters of father id 123)

(the table should have a better name, like litters_fathers)

You could use OR clauses and things to get the litters from several 
fathers, or flip fathers and litters
in the query to get the fathers who participated in a (set of) 
litter(s). (Still a little weird for me:))

Although the queries to do things this way seem more complex, if you 
ever bump into one of those
"I think there won't be more than 4 of these" limits with a case that is 
5+ that will be much worse.



On 03/15/2010 03:54 PM, Bonnie Dalzell wrote:
> 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 at 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
>