Too many relationships / indexes

[neilwebber]

Hello
Sorry to add to the number of 'relationship' posts but a thorough search has failed to turn up anything similar to my problem.

I have a table tblSite
Each geographical site (defined by a PK 'SiteNo') in tblSite can contain multiple occurrences of a large number of types of geographical features (ie multiple 1-many relationships).

Eg each Site can contain
100 trees
10 ponds
5 streams
2 springs and so on.

I could have anything up to 50 or so different types of feature that are related on a 1-many basis to the site, linked by 'SiteNo'

Access prevents any more than 32 indexes per table so in practice that means that you can't have more than 32 relationships with referential integrity enforced on any one table. I thought that I was correct in having a separate table for each feature type and creating 1-many relationships to the central table. The fact that I can't have more than a certain number of relationships suggests that my structure is wrong.

I thought about having one large table with a 'feature type' field plus all the other fields required for each feature type but the info recorded about each feature type is different, leading to a large table with many fields, many of which would be blank. This seems to be un-normalized to me.

So, my question is twofold really.
Have I got my structure right? Is it one of those special cases (we all want to be a special case?) and if so, how do I get round it?
If not, where have I gone wrong?
I thought I was comfortable with my design but coming up against this limit has thrown me a bit and I'm not sure how best to proceed.

regards
Neil

 

[KenHigg]

I think I would use three tables. The site table and a site feature table. There would be a one to many relationship between the site an site feature table. Then a third feature definition table that has a 1 to 1 realtionship with the site feature table.

 

[neilwebber]

Thanks for your response Ken
I'm not certain of what you mean by a 'feature definition' table and how the 1-1 relationship would fit.

I see that I could link the Site table to a table FeatureTable with two fields, SiteNo and FeatureType (tree, pond etc), via a 1-many relationship on SiteNo. I'm a bit stuck as to what you intend after this.
Surely I still need a large amount of tables linked to FeatureTable to contain the info about each possible feature type.

Eg Site1 has two trees, three ponds and 2 streams

So I still need TreeTable, PondTable and StreamTable linked to FeatureTable on a 1-many basis. Expanded to take into account the many different types of features, I'm still going to have to have too many links on one table.

I'm obviously missing the point somewhere along the line - could you expand a little?

thanks
Neil