Getting around the 32-relationship-per-table limit in Access

Question

I am attempting to build a database for inventory control using a large number of tables and enforced relationships, and I just ran into the 32-relationship (index) limit for an Access table (using Access 2007).

Just to clarify: the problem isn't that the Employees table has 32 explicit indexes. Rather, the problem is the limitation on the number of times the Employee table can be referenced in FOREIGN KEY constraints. For example:

CREATE TABLE Employees (employee_number INTEGER NOT NULL UNIQUE)
;
CREATE TABLE Table01 (employee_number INTEGER NOT NULL REFERENCES Employees (employee_number))
;
CREATE TABLE Table02 (employee_number INTEGER NOT NULL REFERENCES Employees (employee_number))
;
CREATE TABLE Table03 (employee_number INTEGER NOT NULL REFERENCES Employees (employee_number))
;

...

CREATE TABLE Table30 (employee_number INTEGER NOT NULL REFERENCES Employees (employee_number))
;
CREATE TABLE Table31 (employee_number INTEGER NOT NULL REFERENCES Employees (employee_number))
;
CREATE TABLE Table32 (employee_number INTEGER NOT NULL REFERENCES Employees (employee_number))
;

An exception is thrown on the last line above, "Could not create index; too many indexes defined.

What options do I have to work around this limitation?

I've heard that creating a duplicate table with a 1:1 relationship is one method. I'm new to database design, so please correct me if I'm wrong; but given a table Employees with 31 indexes, I would create a table Employees2(with one field?) with a 1:1 relationship to Employees and relationships to this new table from any remaining relations in which EmployeeID is a foreign key. What's the best way to ensure the second table is populated alongside the first?

Is there another approach?

Based on the lack of information available, it seems this may be a rare problem with a properly-designed database, or the solution is common knowledge. Forgive the noob!

Update: Immediate consensus is that my design is borked or far too ambitious. This could very well be the case. However, I'd rather have a general design discussion within a separate question, so for the sake of argument, can someone answer this one? If the answer is simply "Don't ever do that" I'll have to accept it.

Answer 1

It is hard for me to believe that an Employee table would need 32 indexes; if it actually does you should consider migrating to at least SQL Express.

Answer 2

Okay so, I've built a lot of applications and I have never needed 32 relationships for a single table.

Now, I could assume you're developing a super kick-ass application that blows everything I've ever written out of the water...

but you're using access.

... So instead I'm going to assume you're doing something wrong and ask, why do you need 32 relationships?

Answer 3

I've run into this limitation a number of times with my apps. And I can assure the other posters that my apps are very well designed.

One problem is that Access creates indexes due to relationships and lookup fields that aren't viewable on the main index property box but they are accessible via DAO collections. These indexes are frequently duplicate indexes to indexes you have created as well.

I have a tool consisting of several forms you import into your BE MDB that allows you to remove the duplicate indexes. As I haven't yet made this available on my website please email me for it.

Answer 4

... I would create a table Employees2(with one field?) with a 1:1 relationship to Employees and relationships to this new table from any remaining relations in which EmployeeID is a foreign key.

That is workable. Presumably your main table might have an Autonumber field as the primary key, or you generate an index number. Your Employees2 table obviously must echo that.

What's the best way to ensure the second table is populated alongside the first?

That depends somewhat on how you are adding records. But in general, of course you must comply with the rules for integrity. This usually comes down to appending to tables in the correct order and ensuring each record is saved before trying to add a related record elsewhere.

Answer 5

I'd suggest just not defining all the relationships/indexes to implementing a 1:1 relationship to get around it. Neither solution is optimal, but the later is going to create a much higher maintenance burden and data anomaly potential.

I am not going to decry the design as quicky as some of the others, but it does have me intrigued. Could you list the fields of the employee table that are foreign keys? There is a good liklihood that some normalization is in order and maybe some of the smart people on SO could make some design suggestions to work around the issue.