Hi AG,

I've been thinking about this one all morning, trying to visualise your
design. I think I now have it. The unusual feature is these 6 production
tables but I can understand how you arrived at this if you are worried about
redundancy - and this debate about redundancy is the crux of the whole
matter, but let's leave the debate to one side for the moment.

What are the adverse consequences of having 6 tables rather than one? I
suggest that if in future you need to add a seventh reagent class/table then
this will be more difficult than adding additional columns to an existing
table. Then there is the difficulty and additional complexity you have
already experienced of defining relationships and queries. Apart from these
two points I can see no obvious 'minefields' ahead.

I believe the relationships you describe to be incorrect but before
explaining what I think they ought to be we have to be clear whether we are
talking about database design or query design.

In database design I prefer to use the term relationship. A relationship is
a permanent association between two tables based on key values and usually
describes the real-life nature of of the data interrelationships. You can
define one and only one relationship between two tables. If you wish - and I
can see no point in defining relationships otherwise - the database manager
will ensure referential integrity, et., etc. Database designers will refer
to relationships in terms of one-to-many, one-to-one, etc.

In query design I prefer the term join. Joins are temporary, transient
associations for the purpose of that query and only that query. You may
define as many joins between two tables as you wish. The joins can be
contrary to the database relationships! Access uses the terminology of
LEFT/RIGHT JOIN and INNER JOIN.

I want to talk about your database relationships. Each row on each of your
6 production tables describes a production batch. Each row on your QtyMade
table describes additional common data for each production batch. The
inference is that the total count of rows across all 6 production tables
should equal the count of rows on the QtyMade table; a pair of rows is needed
to completely describe the batch. Each pair of rows should have the same
primary key, probably BatchCode. (I think this is what you are saying but
I'm not too clear about it.) Now, because QtyMade contains a row for every
batch irrespective of its reagent class, the primary key, BatchCode has to be
unique across your application.

When you come to defining your database relationships it makes no sense to
nominate one side as 'parent' and the other as 'child.' What you need is a
one-to-one relationship between the QtyMade table and each of your production
tables. If you get it right you will see a number 1 at each end of the line
joining the tables in the Access Relationships diagram.

If you store a batch of reagent in more than one location then you need a
one:many relationship between your batch and the inventory location. However
if you always store a batch together in one and only one location then why
not simply add the location attributes to the QtyMade table? But if you
insist on a separate table then again it is a onene relationship and I
suggest the primary key of the Inventory table is also BatchCode.

Finally there is the Package that I like to think of as a shipment. The
Package has a many:many relationship with batches so you need to resolve this
with a cross reference/linking table: Package has a one:many relationship
with your linking table and similarly QtyMade has a one:many relationship
with your linking table. You can construct your linking table with a
compound key of BatchCode and PackageId or give it a meaningless primary key
(autonumber) and include both BatchCode and PackageId as foreign keys.

That's enough for now.

Regards,

Rod

"Access Greenhorn" wrote:

Rod,

You called it pretty close. I work in Virology and am designing the
database to keep track of reagents to be used in diagnostic tests.

What I ended up doing and if you can check it over for any problems I may be
failing to foresee, I have 6 production tables that hold the details of each
batch of 6 different classes of reagents. They are all joined to a table
called QtyMade that holds all of the common information and, of course, how
much was made. The PK of the QtyMade table is comprised of the PKs of the
production tables and acts as the parent in a left join to the child
production tables.

I then have an Inventory table that holds temporary information like where
the reagents are stored. It also is the child in a left join to the QtyMade
table.

The QtyMade table is also a parent in a left join to the Package Details
table that keeps track of what and how may reagents were sent in a package.

Any problems with this setup?

Thanks all,
AG