[AccessD] Design question

Stephen Bond stephen at bondsoftware.co.nz
Tue Jul 9 17:57:20 CDT 2013


Jim, this requirement from the client came in the past month or so, so
it's very much a retrofit situation.  And (surprisingly) it's not even a
Regulating Authority code; it's the code assigned by the automatic milk
weighing software (not mine) .   

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of James Button
Sent: Wednesday, 10 July 2013 9:30 a.m.
To: Stephen
Subject: Re: [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 

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





More information about the AccessD mailing list