[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
>