Unanswered: 32 indexes limit in a big database problem
I'm new here.
I have a problem in a big project I'm working on.
It is a database which consists of more then 120 tables, of which
more then 90 are connected to a "main" table.
Many of them are linked through 1:1 and the others through 1:n
relationship (due to the nature of the project).
basically it is a huge questionnaire, which holds more than 500
questions, and has a very complex inner structure.
All tables have full data-integrity, and are linked via
primary/foreign keys like "Id" of some sort to the "main" table.
That's the intro.
The problem begins at the "32 indexes limit" I've encountered
when trying to add more tables and their foreign keys along with them
to the main table in the realtions view.
What I did was to add two more tables, which only hold
Id's and are related to the "main" table via a 1:1 relationship
and then added every new record once inserted in a form
linked to the "main" table in a "after insert" event
by opening two recrdsets.
That not always worked, but this is another issue.
Can anyone tell me how can I workaround this problem.
Is the "mirror" tables solution correct?
Should I split the database into 3 smaller files and then link the
tables (which does not sound too practical)?
I am using access 2000 on a win98SE computer
but will be moving to win 2000 soon.
I did not add any other indexes other than
those primary/primary foreign keys I've used
to relate the tables with.
Any help would be greatly appreciated since
this problem is holding back the work.
This sounds like a design problem to me. As far as I know the 32 index limit is a per table limit, not a database limit.
By linking every table to the 'main' table, in effect you are saying there is only one entity in your system and virtually everything else is an attribute of that entity. Let me put that another way: you have one object and a properties collection split across 119 other tables!
You need to normalize your database into an object hierarchy by identifying the lower level entities (and their associated attributes). Set up the index links of lower level attribute type tables to point exclusively at the object they describe and then ONLY link the 'main' table to that entity.
In practical terms, if your analysis reveals a group of 12 tables where eleven of them are clearly related to the twelth, you would link the eleven to the twelfth and only link the twelfth to the 'main' table, saving you 11 links.
Taking this approach clearly calls for a rewrite of the code you have already written in order to traverse the hierarchy.