[AccessD] Design question

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