managing more than 32 indexes

[JohnLute]

My database is very complex. It houses packaging specification data and
therefore requires lots and lots of fields and tables.

I have a table that requires much more than 32 indexes. Of course, this
isn't possible. After searching through the forum here I came upon the
solution of creating index tables. I've made my tables but have run into a
problem. First, here's my design:

tblProfiles
txtProfileID (PK)

tblProfilesPKPhysicalAttsIndexes
txtProfileID (PK) Establishes one-to-one relationship with above table.

The problem is that records I create in tblProfiles don't appear in
tblProfilesPKPhysicalAttsIndexes.

Can anyone see something wrong with my design?

Thanks!

 

[John Vinson]

My database is very complex. It houses packaging specification data and
therefore requires lots and lots of fields and tables.

I have a table that requires much more than 32 indexes. Of course, this
isn't possible. After searching through the forum here I came upon the
solution of creating index tables. I've made my tables but have run into a
problem. First, here's my design:

tblProfiles
txtProfileID (PK)

tblProfilesPKPhysicalAttsIndexes
txtProfileID (PK) Establishes one-to-one relationship with above table.

The problem is that records I create in tblProfiles don't appear in
tblProfilesPKPhysicalAttsIndexes.

Can anyone see something wrong with my design?

Just a misunderstanding of how one to one relationships work! They do
NOT - and should not, and cannot - automagically copy records from one
table into another. Instead, such a relationship PREVENTS you from
adding a new record to the "dependent" table unless a corresponding
record already exists in the "parent" table. Strictly speaking, the
relationship should be called a "one to (zero or one)" relationship.

Why do you feel that you must have so many indexes? Access uses
indexes to speed searching and sorting, but non-indexed fields can
still be used for searching or sorting; it's just slower. Do you have
so many foreign keys in this table that you're pushing the 32 index
limit on foreign keys alone? Are you certain that you're not "doubling
up" on indexes? Creating a relationship in the Relationships window
creates an index for you; you should NOT manually add a second index
on that field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JohnLute]

Thanks, John.

Yes - I have many more than 32 foreign keys for this table.
No - I'm not doubling up on indexes.

I get asked these kinds of questions a lot. I'm using Access for a packaging
specification database that includes boxes, cartons, film, cups, bottles,
caps, etc. etc. It's very complex because each type of packaging has many
unique characteristics.

So do you have any ideas how can I get around this limitation of 32 indexes?

Thanks!

 

[John Vinson]

Thanks, John.

Yes - I have many more than 32 foreign keys for this table.
No - I'm not doubling up on indexes.

Didn't think so but it was worth asking! "Never overlook the obvious
solution" - S. Holmes.

I get asked these kinds of questions a lot. I'm using Access for a packaging
specification database that includes boxes, cartons, film, cups, bottles,
caps, etc. etc. It's very complex because each type of packaging has many
unique characteristics.

So do you have any ideas how can I get around this limitation of 32 indexes?

You might want to consider "Subclassing" - the most common legitimate
reason for one-to-one relationships. The way I would do this is to
have a "master" table Packages which contains only those fields in
common to all types of packages; this table would be related
one-to-one to each of a number of other tables for the specific
package types. That is you might have tables named Boxes, Cartons,
Film, Cups, etc. etc.

Each of these tables would have a primary key matching the Packages
table PK, and fields specific for that package type. This would
obviate the indexes problem, I suspect, since the relationships would
be spread amomng all the specific tables. It will make your data entry
forms rather more complex (you'll need a Form for Packages and
multiple Subforms, one for each package type), but it will avoid the
"wide table" dilemma with many unused fields in your main table.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brett Collings [429338]]

Thanks, John.

Yes - I have many more than 32 foreign keys for this table.
No - I'm not doubling up on indexes.

I get asked these kinds of questions a lot. I'm using Access for a packaging
specification database that includes boxes, cartons, film, cups, bottles,
caps, etc. etc. It's very complex because each type of packaging has many
unique characteristics.

John, it sounds like you have a normalization and data structure
issue. I tend to agree with John V that there are other issues here
that we maybe shoudl address to see if it can be sorted.

Is this an inherited database or is it a new one that allows us the
flexibility to make some structural changes before you get to a stage
that the database becomes unusable?

Brett

So do you have any ideas how can I get around this limitation of 32 indexes?

Thanks!

Cheers,
Brett

 

[JohnLute]

Thanks for your time! I've read the yours and Brett's responses and I thought
I'd respond to yours just to keep things simple.

I originally created tblProfiles. This table is designed to store all the
common info for all packaging, finished goods, etc. Within this table is a
field called "Type." This is a combo box that selects types for a Profile.
For example, CG is Corrugated, LD is Lids, ADH is Adhesives, etc.

If I understand you correctly, here's what I need to do:
- Create tblCorrugated with a field named txtProfileID (for relationship
with tblProfiles).
- Move the "Type" field from tblProfiles to tblCorrugated.

This would make things very complicated because I already have many queries
built that reference tblProfiles.Type.

What I can do is create tblCorrugated; tblLids; tblAdhesives, etc. and then
index off of them, however, I don't have a field to move from tblProfiles to
all of these tables. Would just an autonumber and txtProfilesID suffice?

Thanks for your generous help!

 

[John Vinson]

Thanks for your time! I've read the yours and Brett's responses and I thought
I'd respond to yours just to keep things simple.

I originally created tblProfiles. This table is designed to store all the
common info for all packaging, finished goods, etc. Within this table is a
field called "Type." This is a combo box that selects types for a Profile.
For example, CG is Corrugated, LD is Lids, ADH is Adhesives, etc.

If I understand you correctly, here's what I need to do:
- Create tblCorrugated with a field named txtProfileID (for relationship
with tblProfiles).
- Move the "Type" field from tblProfiles to tblCorrugated.

This would make things very complicated because I already have many queries
built that reference tblProfiles.Type.

What I can do is create tblCorrugated; tblLids; tblAdhesives, etc. and then
index off of them, however, I don't have a field to move from tblProfiles to
all of these tables. Would just an autonumber and txtProfilesID suffice?

That's NOT what I would suggest. If a given record in tblProfile - as
appears to be the case - refers to just a particular type, you would
*keep* the Type field (AS A NUMERIC FIELD, not a "combo box" - combo
boxes in tables are an abomination hateful in the eyes of the Access
dieties <g>) in tblProfile.

tblCorrugated would have txtProfileID as its Primary Key, to be used
as a link; it would also have any fields for information which is
specific to just currogated containers ("Thickness" or
"WeightPerSqFoot" or whatever fields are relevant to a particular
container). If you don't have such fields then I'm not certain whether
subclassing would do any good; but you *did* say "each type of
packaging has many unique characteristics." This suggests that those
characteristics are all in one table, requiring too many indexes; if
you move those characteristics into the one-to-one related table, then
you'ld get around the problem, because tblCurrogated would not need
fields such as Viscosity or SettingSpeed, and tblAdhesives would not
need Thickness or WeightPerSqFt.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JohnLute]

Good points, thanks! I'm looking into something like this:

tblProfiles.txtProfileID in a 1-1 relationship with:

tblCorrugatedAttributes.txtProfileID in a 1-1 relationship with:

tblPKCGPhysicalAttributes.txtProfileID
tblPKCGMaterialAttributes.txtProfileID
tblPKCGPerformanceAttributes.txtProfileID
tblPKCGFinishingAttributes.txtProfileID
tblPKCGAdditionalAttributes.txtProfileID

The field to generate the related record in tblCorrugatedAttributes will be
PackagingGroup. This will appear as an option group in forms. The selections
will be Primary, Secondary, Primary and Secondary, or Resource. I've tested
it out and it seems to be working OK.

Any thoughts you might have on this would certainly be appreciated!