[Novalug] Database advice
jbholland at gmail.com
Mon Mar 15 16:07:58 EDT 2010
That's pretty weird about the multi-father litters. Learn something
As far as fixed number of columns, you can always avoid that by having a
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
>> <------- owned from 1/1/2006 to present ----------> owner Fred
>> <------- 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
>>>> 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
>>> 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
>>> 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
>>> 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
More information about the Novalug