cascading delete and multiple primary keys...ugggg

[salmonella]

I have a REAL problem. I am writing a Db that will track bacteria, diagnostic
tests, etc. in my laboratory. At present it has 37 tables and growing. The
root of the problem is that in many cases I need to collect the same data in
relation to several other types of data. For example, I need to store
(storage table) things from: samples (sample table), DNA preparations (dna
tests table), bacteria (isolate table), etc. What I did was, instead of
creating separate (but identical) storage tables to join to tables sample,
bacteria, etc. I used just one tbl called storage to join to each of these
other tables. The problem is that because I am joining storage to multiple
tables, I cannot use referential integrity and Cascading Delete to delete it.
So, now I have a database that will just love to collect orphan records.

Because not all records are redundant what I actually have is referential
integrity and cascading delete set on a chain of tables then a “fire breakâ€
then cascading delete again. For example, tbls A-B-C-____D-E-F where there is
cascading delete in the join between all the tables except C and D (storage).

Anyways, I guess that my question is, is there a straightforward way to
deal with this situation (e.g. cascading delete through code or a utility sub
that will remove orphan records), or should I just create separate (but
identical) “storage†tables so that I can use the cascading delete in the
join? Bear in mind that separate tables will balloon the Db GREATLY and
increase the total number of tables by 3 to 4 times (~60-90 tables) and the
Mb size will double or triple- (I possibly can use just one form by just
changing the record source with code as needed).

Being new to all this I am hoping real hard that I am missing something
simple, however if not, does anyone know how to deal with this problem?????
For example, if there was a delete event for table records like there is for
forms, then I could use code to jump the fire break each time a record was
deleted in C above to delete related records in D which, when deleted, would
invoke cascading delete in E, F etc.

ANY ideas on this would be appreciated.


Thanks!!

 

[MAT4Ron]

Sounds like? in my Work Order db when I need to use the contact info several
times for different purposes on a work order(table). The answer is simple; I
create a Self Join Query (SJQ) and in my lookup i use the SJQ instead of the
table.
Don’t let the term SJQ scare you it's just a query but when you are building
the query that pulls together all the information you want to store in your
main table you use the SJQ's just like you would a table.
To make it so the data is clearly defined I usually format it differently.
For example the Customer field would be formatted Last, First, Rank and the
person the Work Order is routed to would be formatted Rank, Last, Shop. But
it is all the same "Customer Table".

 

[salmonella]

Hi MAT4ron
Thanks, but I don't think this is it. A sjq, as far as i understand it,
will make a join from data within the same table using duplicate data is
different columns to make the join.

What I have is simply several totally different tables which i will collect
the same type of data on. in other words, I store info (storage table) on the
freezer, box# etc on a particular sample (sample table) or store the same
type of info for a bacterium i isolate ( bacteria table). It is sort of like
having a table called PHOTO and using it to collect photos for several tables
(such as personnel, merchandize, events, etc).

I hope this is clearer- any ideas?

thanks,

 

[Albert D.Kallal]

What I have is simply several totally different tables which i will

collect
the same type of data on.

If you are talking about the "same" data..but different tests, then you data
is NOT normalized.

We often see a person building 12 tables for each month of sales data. The
simple solution would have been to add ONE column to the sales data tables,
and presto...you now don't have 12 tables..but one table with the new column
for the month (or, even better..the date).

So, if you have similar tables, but ONLY distinguished by different tests,
or different "contains", then you have a bad normalized database. Why not
just add on extra column that allows you to enter the type of container?
Even more benefits is that when you need some new type of container...you
don't have to create a new table....but simply add a new container type.

If your design requires you to add a new table for each type of new
container, then something is wrong....

The whole concept of data modeling is why Boeing can build a system with not
very many tables, but that table can hold any type of part assembly, and
also do costing on those parts for that plane. So, one does NOT have to
create a new table for wire assemblies or a new table for a pipe assembly.

It sounds to me that you have too many tables already. While the most
common mistakes is to try and put everything into ONE table. (normalizing
means that you split things out to several tables). However, often a common
problem is trying to make a new table for data that can be simply
distinguished by adding one column to denote the type of test (or type of
container, or whatever it is we are trying to model in the real world).

If you normlize your data...you will useally results in LESS tables being
needed overall...


ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

 

[salmonella]

Thanks for the thoughts but no, I don’t have a table for each test and I
'thought' it was fairly well normalized.

BUT, you got me thinking. There is admittedly one area where it probably is
not normalized (I just did not see it before because I was following
laboratory flow instead of database design- a bit embarrassing!) and by
redesigning this one thing I probably can reduce greatly the number of tables
which I will have to, in essence, duplicate.

The problem still is that I will have to take the same type of data on very
different data tbls (from microscopy to post mortems to DNA analysis- thus
the large number of tables). For example, I may want to take a picture of a
liver in a cadaver or a bacterium under the microscope, or document the
results of a laboratory test with a photo. I still can not see a way around
this BUT, I think I may be able to reduce their number to where it is not a
problem.


Many thanks for helping me see things critically!

 

[salmonella]

Albert,
If I could bother you one last time. I thought that I saw a way to reduce
the problem, but I was wrong, so……………. I believe that I am going to just put
in the tables and be done with it. However, before I make this step I have
two questions for you.

First, is there any way to trigger a delete event on a record in a table or
record set. If there is, as there is for forms, then it would be fairly easy
to use code to solve the cascading delete problem.

Second, am I missing something with my normalization? I will use a
simplified example that would apply to the Db as a whole. I have a Sample
table that holds data on a sample (such as blood) and, on the many side, an
Isolate table that holds data on bacteria, viruses, etc. that are isolated
from the blood. For data in both theses tables I want to collect the same
type of information, for example a picture of the blood or bacteria, perform
a particular test on the sample or bacteria, store away some of the sample or
bacteria, etc. Thus I have two tables (Sample and Isolate) that I cannot
consolidate into one table because many different isolates can be obtained
from one particular sample, yet I have to collect the same type of data on
each. This is totally different than something like a table for each month of
the year. Do you see where I might have made a problem?

This is an interesting problem that appears not to generally show up with
people. For example, I have two other databases, one that controls grants,
students, prints progress reports etc and another that does quality control
on reagents, diagnostics, logs test results, generates work orders, etc and
neither of them have even one instance of this problem.


Many thanks!

 

[Albert D.Kallal]

First, is there any way to trigger a delete event on a record in a table

or
record set. If there is, as there is for forms, then it would be fairly
easy
to use code to solve the cascading delete problem.

No...but this just means you need to restrict deletions to your forms
anyway.

For the "major" tables, you likely can/should build a routine you call to do
the delete..

Call MainTableDelete(id)

For data in both theses tables I want to collect the same
type of information, for example a picture of the blood or bacteria,
perform
a particular test on the sample or bacteria, store away some of the sample
or
bacteria, etc. Thus I have two tables (Sample and Isolate) that I cannot
consolidate into one table because many different isolates can be obtained
from one particular sample, yet I have to collect the same type of data on
each. This is totally different than something like a table for each month
of
the year. Do you see where I might have made a problem?

This is an interesting problem that appears not to generally show up with
people. For example, I have two other databases, one that controls grants,
students, prints progress reports etc and another that does quality
control
on reagents, diagnostics, logs test results, generates work orders, etc
and
neither of them have even one instance of this problem.

Actaully, the two above problems are simular as far as I can see.

Asumming that you have a "grant"...and then "many" things are attached to
that grant...


Assuming you have a "test", and "many" things are attached to that test

tblTest - name of test, reasons for test, who approoved test
- start date of test....etc. etc etc. etc.

many side ---->tblResults

DateTime TestType CellCount Bacteria Count Picture
Dec 3 3:30 pm Sample 2000 3000 "path
to picutre"
Dec 4 4:00 pm Sample 2200 3200 "path
to picutre"
Dec 4 2:00 pm Isloate 2200 3200
"path to picutre"
Dec 5 2:15 pm Isloate 1200 2200
"path to picutre"

The above seems just like the "grants". ....You have a "many" side table
that can hold many test results..some of those results are samples, and some
are Isolates. Further, each test as above has provisions for a picture of
the results (remember, store the path name to the picture...don't put the
actual picture in the database). The above is just a simply "main" form for
the lab test name (tblTest), and then the "many" side is the a sub-form
where you can enter the test type (Sample/Isolate).

How is the above different then the grants example?

 

[salmonella]

Hi Albert

MS just ate the message i wrote you. Anyway, in a nut shell- the other
databases are not exactly the same but the diff is hard to describe in
writting. Anyway, your suggestions where along the right line and I was able
to re-arrage some tables and it seems that it will work almost perfectly.
Again, thanks for the suggestions, they really helped!!