[AccessD] Design question

>From my little knowledge of the paperwork associated with livestock farming,
I suspect that each animal will have it's own - Ministry/Regulator Authority 
issued/authorised ID-reference-code.
All returns/reports, movements, treatments etc. would be reported, or at 
least recorded for evidence should there be any query about appropriate 
records for that animal, both within that farm unit, and wherever such 
animals came from, or went to - or got acquired by unagreed transfer methods 
(theft etc).

So, I'm surprised that you do not already have that id code as the primary 
key on the records
However I would avoid having the name of the table containing the other 
data, using a typecode such that the other data can be included in a select 
such as eithr of the following approaches
select li,st from midtable, union data2table,  data3table,  data4table,
where id =id
and

select li,st from midtable, union data2table
where id =id
and sex = "Male"
and typecode = "Sire"

And (fighting talk) remembering "Normalisation" is a great  technique, but 
it's a "Good jugement" call to decide when it's better not to go fully 
Normalised

JimB

----- Original Message ----- 
From: "Stephen Bond" <stephen at bondsoftware.co.nz>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Tuesday, July 09, 2013 9:59 PM
Subject: [AccessD] Design question


> 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