Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002

    Question Unanswered: 32 indexes limit in a big database problem

    Hello everyone.

    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.

    Thank you for your time and effort,

    Last edited by ubiq; 02-24-02 at 06:16.

  2. #2
    Join Date
    Mar 2002
    Calgary, Alberta
    Good Morning Ubiq,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts