Modifying a table

[Dave]

The continuing saga of my database.
I have built a database to track the storage of vials of frozen cells.
One of my tables includes "Canisters" of which there are 26, A through
Z. Each canister has 9 "Trays". Each tray holds 56 "Spots". They are
related to a sample table by a secondary key "SampleID". I have since
populated all these spots with the appropriate vial. Now I see that
there may be some poor table design on my part as I have each Canister
repeated 56 X 9 times and each tray repeated 26 X 56 times and each
spot repeated 26 X 9 times in the table. Probably not good for
performance sake, although it's not bad. Is there a way to fix this
that won't disrupt all my queries and forms that are using this table?

Thanks in advance for all your help these past few weeks,

Dave B

 

[Ken Sheridan]

Dave:

I'm afraid I've missed the saga to date, but it sounds to me like the
relationship is the wrong way round, hence the redundancy. I'd have thought
that you need tables Canisters (26 rows); Trays (9 rows); Spots (56 rows),
and the Samples table should have Canister, Tray and Spot foreign key columns
referencing the keys of the other three. The three foreign key columns in
Samples should be compositely indexed uniquely as, if I understand the
scenario correctly, each Canister/Tray/Spot location can only be occupied by
one distinct sample.

Your current table structure would actually be appropriate in conjunction
with the three tables suggested above if it was modelling a many-to-many
relationship i.e. if each sample could be held in more than one spot, and
each spot could hold more than one sample, but that sounds like an
impossibility to me as a layman at least.

The model I suggest would, if the unique index on the Canister, Tray and
Spot columns was removed, be appropriate for modelling a one-to-many
relationship, i.e. where each sample could be stored in more than one spot.
That does not seem totally out of the question to me, as it depends on the
definition of 'sample' and would simply mean that a 'sample' might be compose
of more than one vial. From your description it sounds to me like each vial,
and thus each spot position holds a distinct 'sample', however.

Joining all four tables will produce a result set equivalent to your
existing table (you'd need to use outer joins if any spots are unfilled and
you want those included), which might be OK for any reports you are using,
but as the basis of data entry is radically different from your present table
you would have to rethink your forms I'd imagine.

My advice in a situation like this is always based on the principle that the
first thing to do when in a hole is to stop digging, but having invested time
and effort in the current design whether you would be prepared now to
radically change the logical model is for you to judge of course. If you do
retain the present table, however, its essential that you create a composite
unique index on the Canister, Tray, Spot and Sample ID columns if you have
not already done so. They could in fact be made the table's primary key.
This would not eliminate the redundancy, but would prevent any inadvertent
update anomalies by disallowing more than one sample to be recorded at the
same Canister/Tray/Spot location.

With the normalized model as I suggest it above a data entry form based on
the Samples table would simply have three combo boxes bound to the Canister,
Tray and Spot foreign key columns, so building a form for that would be a
simple task. Recasting the data would also be quite simple once the
Canisters, Trays and Spots tables were set up and the empty foreign key
columns added to the Samples table, merely requiring a single update query
joining the Samples table to the current table on SampleID. Without knowing
what other functionality you've included in your database, however, I can't
comment on what might be necessary beyond this.

Ken Sheridan
Stafford, England

 

[Dave]

You're correct. Each spot can hold only one sample. I've set up an
index in the Locations table to prevent duplicate entry of Canister,
tray and spot. I'm just looking for a way to change the existing table
without messing up the current forms and queries, if there is such a
way. I've found a wizard in tools menu that may do it but I have no
experience with it. Has anybody else used this wizard? Any comments on
it?

Dave B

 

[Dave]

It appears that I can separate the table with the wizard and things
stay the same. Good wizard. But, I need a unique index on three fields:
Canister, Tray and spot. It was no problem when those three fields were
in the same table. How can I create a unique index for those three
fields in three separate tables?

Dave B

 

[Ken Sheridan]

Dave:

You can't create an index which includes columns from different tables, but
I don't think you need to in fact. I'm not absolutely sure what structure
you now have, but I'm guessing you now have a table similar to your original
one, but which now includes foreign key columns referencing the primary keys
of the new Canisters, Trays and Spots tables, so you can create a unique
index on those three foreign key columns, which is what's required to
maintain the integrity of the data as this means that each canister/tray/spot
position can only appear once in the table. Similarly the SampleID column in
this table should be indexed uniquely as each sample can appear only once in
the table.

By having the three new tables you can now enforce referential integrity in
relationships between these and the table which equates to your original one.
That ensures that you can only have values in the foreign key columns in
that table which exist in the three referenced tables. The wizard might
already have created such enforced relationships for you. If so you can
examine them in the relationships window.

If I'm right about the structure you now have, the table which equates to
the original one is in fact modelling a relationship between the Samples
table and the three new ones. That's a far better structure than your
original one, even though it goes somewhat beyond what's actually necessary.
Tables are frequently used to model relationships but generally these are
many-to-many relationships resolved into two or more one-to-many
relationships, e.g. Orders---<Order Details>---Products in the sample
Northwind databse. In your case your table includes a one-to-one
relationship with Samples. The use of a table to model a one-to-many
relationship by means of one one-to-one and one one-to-many relationships is
a recognized technique, and is intended to eliminate the use of Null foreign
keys. That possibility doesn't actually arise in your case, but that doesn’t
mean there's a problem with the model, its just slightly more complex than is
necessary.

Ken Sheridan
Stafford, England