help!!

[salmonella]

I have not been able to get much of a response to this problem. Please help
if you can. Can I use referential integrity and have one table on the many
side joined to more than 1 table on the one side?

for example can i have a table called photo joined to tables "sample" and
"isolate" or do I have to make two identical photo tables (photo_sample and
photo_isolate)?
If so, this would cause a MASSIVE increase in the number of tables in my db!!

PLease help. I can't work on it until I get this design problem solved!!!

 

[John W. Vinson/MVP]

I have not been able to get much of a response to this problem. Please help
if you can. Can I use referential integrity and have one table on the many
side joined to more than 1 table on the one side?

No, not with referential integrity enforced.

for example can i have a table called photo joined to tables "sample" and
"isolate" or do I have to make two identical photo tables (photo_sample
and
photo_isolate)?
If so, this would cause a MASSIVE increase in the number of tables in my
db!!

Well... not two *PHOTO* tables; I'd suggest that you have a single Photo
table and as many "resolver" tables as you need to link it to the Sample and
Isolate tables. These tables would have the PhotoID and (say) the SampleID
as a joint two-field primary key.

PLease help. I can't work on it until I get this design problem solved!!!

John W. Vinson/MVP

 

[PC Datasheet]

Yes! Suppose you had an OrderDetails table that looked like:
TblOrderDetails
OrderDetailsID
OrderID
ProductID
ColorID
SizeID
Quantity
Price

You would have relationships to TblOrder, TblProduct, TblColor and TblSize
and all these relationships could have referential integrity enforced.

 

[salmonella]

Thanks for the help. However it is not clear to me how a revolver table will
help. I have used them between two tables with a many to many relationship,
however in this case it is between one table and many tables. The problem is
that I have a db that will take data on a sample source, then a sample taken
from the source, then on the culture of the sample, then on bacteria isolated
form the culture, etc., and for each of the above tables (sample source,
sample, etc) I want to take similar data (photos, diagnostic tests, etc).
Because of this I have either to make copies of all the tables, and forms for
(photos, diagnostics, etc) change the names and join one set to sample source
another to sample, etc. I actually can use just one table and it works fine,
but not if I use RI, in which case if a fill in photo data for sample source,
I will be asked for data for that same photo record from sample, culture,
etc.

So…… This must be a situation that comes up a lot (wanting the same data for
different tables). The relationship window, for example, lets me add two
photo tables but then will redraw the joins to just one photo table. Anyway,
do people just make duplicate tables as I outlined above? i.e.
tblphoto_sample_source, tblphoto_sample, etc??

Because this is basic to the design of the database I am afraid that if I do
the wrong thing here then I would literally have to start over to fix the
problem.

ANY help is GREATLY APPRECIATED!!!

 

[salmonella]

Thanks, but this is not the same situation i have. What you have is a strait
one table to one table. I have one-table to many tables. John Vinson also
gave his thoughts and I am putting below my reply to him so that it may
explain why the example you have is not the same situation. MANY THANKS!

Response to John:
Thanks for the help. However it is not clear to me how a revolver table will
help. I have used them between two tables with a many to many relationship,
however in this case it is between one table and many tables. The problem is
that I have a db that will take data on a sample source, then a sample taken
from the source, then on the culture of the sample, then on bacteria isolated
form the culture, etc., and for each of the above tables (sample source,
sample, etc) I want to take similar data (photos, diagnostic tests, etc).
Because of this I have either to make copies of all the tables, and forms for
(photos, diagnostics, etc) change the names and join one set to sample source
another to sample, etc. I actually can use just one table and it works fine,
but not if I use RI, in which case if a fill in photo data for sample source,
I will be asked for data for that same photo record from sample, culture,
etc.

So…… This must be a situation that comes up a lot (wanting the same data for
different tables). The relationship window, for example, lets me add two
photo tables but then will redraw the joins to just one photo table. Anyway,
do people just make duplicate tables as I outlined above? i.e.
tblphoto_sample_source, tblphoto_sample, etc??

Because this is basic to the design of the database I am afraid that if I do
the wrong thing here then I would literally have to start over to fix the
problem.

ANY help is GREATLY APPRECIATED!!!

 

[Tim Ferguson]

The problem is
that I have a db that will take data on a sample source, then a sample
taken from the source, then on the culture of the sample, then on
bacteria isolated form the culture, etc., and for each of the above
tables (sample source, sample, etc)

This is a fairly common situation, and it's solved using a technique
called subtyping.

You need one entity (supertype) that represents the commonality between
all the subtypes. Let's call it ThingsThatCanBeSampled. It might have
fields like

ThingID counter, primary Key
Location which lab it's kept in
OwnedBy responsible lab technician
etc


The subtypes are represented by tables specific to what they are:

Cultures(*ThingID+, DateInoculated, MinTemp, MaxTemp)

Sources(*ThingID+, RoomNumber, TimeOfDay, TimeSinceLastVisited)

Bacteria(*ThingID+, Genus, Species, Type, PhageNo, etc)


The important thing to note is one-to-one relationship between
ThingsThatCanBeSampled.ThingID and Cultures.ThingID etc. This means that
all Cultures (and all sources and all bacteria, etc) have to be members
of ThingsThatCanBeSampled. It is not easy in Jet to ensure that a single
ThingThatCanBeSampled cannot be a member of more than one subtype, but is
easy in real DBMSs like SQL-Server that have triggers. That said, I think
it is possible in Access/Jet although I have not tested it.


Finally, the Samples table has a FK that references the supertype, not
the subtype:

Samples(*SampleNum, ThingID+, PositiveOrNegative, ScaledResult, etc)



Hope that makes some sense. I am having difficulty visualising how a
sample taken from a bacterium is the same as a sample taken from a
Source, but I guess that's your business not mine.

All the best


Tim F