I have a database that I believe is normalized. There is a tables called SAMPLES which contains sampleID as the primary key. I have 29 tables related to SAMPLES each of which has sampleID as a foreign key. All of these relationships are one to many and enforce referential integrity.
My SAMPLES table stores the information on when and how a scientific sample (or samples) was collected with fields that include:
+ siteID (foreign key)
+ sampleID (primary key)
+ dateSample
+ crew
+ method
+ comments
Each of the 29 data table includes the data generated from each sampleID, e.g.
+ sampleID (foreign key)
+ wetMass_g
+ dryMass_g
+ ashedMass_g
+ organicMass_g
+ pctOrganic
+ replicateID
+ dateAnalyzed
There are 29 different data tables, one for each type of data: sediment data, nutrients data, carbon data, chlorophyll data, etc, etc. The sampleID allows me to pull together all of the different types of samples collected during the same sampling event.
I am trying to add a new table, lets call it DATA30, which also has the same relationship to SAMPLES as the rest. However, when I create the relationship in Access it gives me an error saying that there are too many indexes on the SAMPLES table. I know there is a limit of 32 indexes within a table, but everything I read seems to indicate that that is 32 indexed fields within a single table. My SAMPLES table has one primary key and one foreign key. It is also linked to 29 other tables as described above. Are those 29 relationships counted as indexes in the SAMPLES table? It seems odd that I couldn't use the sampleID index to enforce referential integrity for more than 29 tables.
Is my understanding of this wrong? Should my database be organized differently? Any help would be appreciated.
