parent table with 90 children?

[jason]

How do I link 90 child tables to a single parent table?

Access only allows 30 indexes to one table. We would like
to have referential integrity on all of them.

Should I create more "levels of organization"?


Thank You ,
Jason

 

[Scott McDaniel]

Not sure what you mean by "levels of organization" ... however I suspect
your table design is flawed. What are you trying to accomplish, and what is
you basic table structure now?

 

[Jeff Boyce]

Jason

I'll echo Scott's observation...

What kind of data are you working with (or have organized in such a way)
that it takes 90 separate tables of "many" data to show all of a "one"
tables' relationships? This could be an unusual situation, or it may be
that your normalization exercise is incomplete.

More info, please...

Jeff Boyce
<Access MVP>

 

[jason]

Thank you for the response. This is the database for a
research lab in Neuroscience. So , the master table
contains information on each subject who enters our study.
It is the only table which contains all of the subjects.
Depending on at what point they enter the study, subjects
engage in a battery of neurophysiologic tests. The 90
tables actually represent data from about 50 different
tests. The reason we have it all in one db is so that we
can query freely between all of the tables. There are no
tests that all of the subjects have taken.
Currently, I have one master table. The master table leads
out to three what I call "gate" tables whose function it
is to provide more space for the indexes. The gate tables
contain the subject number of all of our subjects, taken
from the master table. Each "gate" table branches out to
30 tables that contain our data.
Is this a crude design ?
Jason

 

[TC]

I think you'll find that 90 tables is way off beam.

If subjects take tests, & tests have results, & there are different result
types (eg. numeric, text, whatever) for various tests, this is what you
would normally have.

A table for subject information:

tblSubject
SubjectID (PK)
name, date of birth etc.

Two tables to define what tests are available, and what are the expected
result type(s) for each test:

tblTest
TestID (PK)
test description

tblTestResult
TestID ( composite )
ResultNo ( primary key )
ResultType (N=number, T=text, whatever)

Two more tables to hold the >actual< tests & results for each subject:

tblActualTest
SubjectID ( composite )
TestID ( primary key)
test date, technician code, etc.

tblActualResult
SubjectID ( composite )
TestID ( primary )
ResultNo ( key )
ResultValue

That structure - using just 5 tables - will let any number of subjects take
any number of tests, each test having any number of results.

I suggest you read this article:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

 

[TC]

Oops, the PK of tblActualTest must include TestDate, since a subject might
take the same test several times, on different dates. Similarly for
tblActualResult.

TC
(snip)