[AccessD] Design question

OK, forget my previous question, this has just come through.  Will have
a look.  There is a huge amount of re-structuring with this.  I'll give
the client a $-figure and see what happens :-)
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
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com