Stephen Bond
stephen at bondsoftware.co.nz
Wed Jul 10 16:55:13 CDT 2013
Thanks to all for your input. I have re-visited the whole situation with the client and have decided to go down the track suggested by Stuart. This also allows us to keep the Lactation/Milk Production against the Ewe table only (apparently their on-farm software allows male animals to have milk recorded ;-). There'll be re-engineering costs associated, but hey I'm not arguing. Onward and upward. Stephen -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Wednesday, 10 July 2013 10:16 a.m. To: Stephen Subject: Re: [AccessD] Design question How about tblAnimals as your primary table. Includes PK, Rego, Sex (Sire.Ewe) and any common characteristics of both sexes. Then tblEwes and tblSires are child tables with fields for the Sex explicit characteristics and a FK pointing to tblAnimals. (Don't use Registration number as the PK.FK link - if you do, you can guarantee that at some point in the future they are going to want to change the rego number - maybe with a breed indicator in it or something similar) -- Stuart On 10 Jul 2013 at 8:59, Stephen Bond wrote: > Here's a design question I've never had to face before ... someone here > will have for sure. > > I am in continuous development of a system which logs and analyses milk > production from a sheep farm. Recently the client has requested an > (another) upgrade, this one to store some genetic data as well. So now > we have 2 animal tables, tblEwes and tblSires. > > As part of the upgrade, a new Registration number is required for each > animal. This must be unique across both tables. How to implement? > Should I put in another table called, say, tblAnimalReg, with the > Registration number as PK? And also store in this table the PK and > tablename of the animal it refers to. Something strikes me as a bit > weak about this ... but I can't put my finger on it. Ideas please. > > Stephen Bond > > > -- > AccessD mailing list > AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> > http://databaseadvisors.com/mailman/listinfo/accessd <http://databaseadvisors.com/mailman/listinfo/accessd> > Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com> > -- AccessD mailing list AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/accessd <http://databaseadvisors.com/mailman/listinfo/accessd> Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com>