Access table column indexing

[Girls3Mut1]

I know that Access has a table limit of 32 indexes. I have a table with one primary key and that primary key column is indexed with no duplicates allowed. I have 36 other tables that would like to have a foreign key relationship to this table's primary key. Only 32 are accepted due to the index maximum. Any thoughts on how to deal with this limitation. I have given thoughts to how I might change the database structure, but I think what I have is optimal. Thanks in advance for any help with this.

 

[GingerR]

Hi--ok, this is screaming for me to ask: 36 tables? Can we see your table structure? Seems like it might not be normalized?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[TheAceMan1]

How are ya Girls3Mut1 . . .

[blue]I have given thoughts to how I might change the database structure, but [purple]I think what I have is optimal.[/purple][/blue]

With that many tables I wouldn't be so sure.

There is a way (called an [blue]extension table[/blue]) but it violates [blue]2nd Normal Form[/blue]. It would be better an easier if you could cut the tables down. Have a serious look at the following link ... then go back and look at your design!

Fundamentals of Relational Database Design

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Girls3Mut1]

I would be happy to share the structure with you. What's the best way to get the file to you?

 

[Girls3Mut1]

Perhaps I can share it this way. The 1:1 relationship is the problem area.

 

[MajP]

Yeah, you could get this down to about 6 tables. This is not properly normalized.

combine all datasheet tables
combind all level tables
combine all notes tables

When I look at tables and they are the same fields but the Category of information is different that tells me you have redundant tables. Whats the difference between AT datasheet and CV datasheet? Nothing. Just the additional field type of datasheet needs to be added.

 

[TheAceMan1]

Girls3Mut1 . . .

Completely agree with [blue]MajP[/blue]. The first thing noticed is the structure is very verbose/redundant in its field names. You need to learn how to think [blue]Access[/blue] and not [purple]Excel[/purple]. Here's another link that should help:

Description of database normalization basics in Access 2000

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Girls3Mut1]

I understand your comments. Datasheets do have unique columns based on the datasheet type and so if I combine them into one table, then it would seem that I would violate 2nd normal form since all columns are not related to the primary key. However, I could reduce the number of datasheet tables by making them more class-based according to the datasheet type and that could potentially cut the table count in half. I appreciate your very instructive commentary very much; I am not a programmer by profession and I have attempted to learn this on my own.

 

[Girls3Mut1]

Forget the previous comment. That whole mess is now down to four tables and I can already appreciate the ease of building my sql queries. I use Sybase Powerbuilder on the front end. Thanks again!

 

[MajP]

Datasheets do have unique columns based on the datasheet type and so if I combine them into one table, then it would seem that I would violate 2nd normal form since all columns are not related to the primary key.

I am not sure what you are referring to, but as far as I can see these tables all have the exact same fields. You have several groups of redundant tables. I seriously think you could merge 30-40 tables into 3 or 4.

A db developer looking at this image will immediately see there are some big issues, and this is just a very small handful. But if you have two or more tables with the same fields your db is not properly designed.

 

[Girls3Mut1]

I do respect your commentary and in my previous post I mentioned that I was able to recognize the duplication and I have reduced this to four tables, which include the following:
tbl_instrument_list
tbl_datasheets
tbl_junction_notes
tbl_notes

I now see the error in my ways and I thank for taking the time to review this on my behalf.

 

[MajP]

That looks good,and I am guessing everything will get a lot easier to design now.