[AccessD] Design question

ROTFL.  
So give me your take, Stuart.  

You are right, there are tens of thousands of Ewes and only a few
hundred Rams, and the data stored for each is dissimilar in not a few
places.  Each milking animal requires (but won't always have) a SireID
and a DamID with the express purpose of progeny selection for future
milk production.  This all started out a couple of years ago when the
customer came to me with a spreadsheet of milk production values, and
the whole ethos has developed around Lactations and Milk Production ...
up till now.  Hence the angst associated with a fairly major broadening
of the scope, including the Rams (Sire) table and the associated inter-
and intra-table pointers.  

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:31 a.m.
To: Stephen
Subject: Re: [AccessD] Design question

Multiple suggestions to have a single table with fields for sex specific
characteristics?

I'm shocked, shocked I say! :-)

That's the sort of thing I'd expect from first year university students,
not developers with years 
of experience.

-- 
Stuart

On 9 Jul 2013 at 16:40, Dan Waters wrote:

> Hi Stephen,
> 
> I would suggest combining these two tables into just tblSheepMain (or
> similar).  In that table one of the columns will be Sex, and the only
two
> choices will be Ewe or Sire (or male/female).  This gives you
precisely one
> unique primary key for each animal.
> 
> With this table in place, you can just add one (or more) columns for
the
> genetic data, which will apply to each animal.  
> 
> I would also guess that some columns in tblSheepMain might only apply
to the
> males and some only to the females - and that's fine.  You might also
need a
> related table if you collect the same set of data about each animal on
a
> periodic basis (such as volume of milk from each female per day,
number of
> calves sired per year for each male, etc.).
> 
> Good Luck!
> Dan
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stephen
Bond
> Sent: Tuesday, July 09, 2013 4:00 PM
> To: Access Developers discussion and problem solving
> 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
> 


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