Too Many Indexes

[AccessIdiot]

Hello all,

I am creating a database for archaeologists. For a given artifact there are several attributes: color, shape, size, usage, etc. I have created a table for each attribute, and then created a master table that holds the primary key of each attribute table. When I create a relationship I'd like to do all three of the standards: enforce referential integrity, cascade updates, and cascade deletes. The problem is that I reach the 32 indexes limit pretty quickly. What can I do to fix this? I'm not sure I can break the tables out any more than they are, and I'd hate to have to forsake the cascading update/delete.

Thoughts?

Thanks in advance!

[Scott Price]

I fail to see the reason for one table for each attribute.

Given Artifact A: It can have many Attributes. That means One to Many, therefore needing only two tables... Am I not understanding something vital to your situation that makes one table for each attribute necessary?

Two tables would be far below this index limit, right?

Regards,
Scott

[Scott Price]

Excuse me, You actually have a Many to Many relationship between Artifacts and Attributes. However, even with this, it really only requires three tables...

Regards,
Scott

[FishVal]

Just subscribing.

[NeoPa]

So, what are you doing with an archeological database now?
Is this a hobby or have you changed jobs?

Of course I'm subscribing too ;)

[AccessIdiot]

Hey sorry for the delayed response. For some reason I fail to get an email when someone posts in the thread, even though I have it set to do so. Also, I'm working on this when my other projects are slow, so I can only get to this intermittently.

Anyway, to answer NeoPa's question, I am a GIS Specialist with a little knowledge of relational databases. The GIS staff at my company offer support to the other disciplines in our office: fisheries biologist, wildlife biologist, wetland scientists, environmental planners, and cultural resources (archaeologists). I got my hands dirty in Access building a db + forms for some fisheries folks last year (they love it by the way - thanks for all your help on that one!).

To offer more on the current topic: when the archies find an artifact they have to fill out a form describing what they found. So let's say they find something called a groundstone. The form calls for the following:

material type (list of materials - a table)
use type (list of possible uses - a table)
how complete is the artifact (possible choices - a table)
texture (range of possible textures - a table)
edge shape
level of use
shape
color
residues or exterior alterations
etc

Every single one of those listed above has choices. Some of these things can be used for other artifacts, like fire-cracked rocks or flakes (think: making an arrowhead). So I thought I'd create tables for each artifact attribute (like material type, color, shape, etc) and then create a "master" table for each artifact, like one table for groundstones, one table for fire-cracked rocks, etc. Then I'd use foreign keys from the different tables of attributes (material type, color, shape) to put in the master tables (groundstone master, flake master, etc) so I could have a bunch of dropdowns on the forms.

Sounds great in theory, but again I'm limited to how many table connections I can make while maintaining integrity. :-(

I'm thinking I'm going to have to abandon some of the tables and just have lists in the form?

I hope that makes more sense.

[NeoPa]

I was thinking of suggesting an Attributes table ([tblAttribute]) which would have records with a complex unique index (can be PK but PK can alternatively be AutoNumber if preferred - many do) made up of an attribute type (or type code) as well as a reference for each attribute option. The main data of each record would be the text of the option.

This wouldn't reduce the number of links but would make the structure of the DB a little less complex.

How many different attribute types would your DB require?

Lastly, bearing in mind that the processing of each different type of master record would have many similarities (I assume), would it not be a good idea to follow the same approach in the (single) master table too?

PS. What does GIS stand for?

[NeoPa]

As far as em notifications go there is a little problem still.

Every time you post again (to a thread which is marked for em notification) it resets this setting (for the thread) to none :(

I no longer rely on this but if you want to for a limited number of threads, simply reset this to your desired setting every time after you post. Nightmare if you have loads of threads, but manageable if only a couple.

[AccessIdiot]

I was thinking of suggesting an Attributes table ([tblAttribute]) which would have records with a complex unique index (can be PK but PK can alternatively be AutoNumber if preferred - many do) made up of an attribute type (or type code) as well as a reference for each attribute option. The main data of each record would be the text of the option.

This wouldn't reduce the number of links but would make the structure of the DB a little less complex.

I think that is sort of what I have now. For example, I have a colors table with an autonumber PK (which I use as a FK in the master table), a code that is an abbreviation of the color, and a full name of the color. So it would look like

1  BL   Black
2  GR  Green
3  RD  Red
 

etc. That way I can use the code in the form but the full name in a report. This color table is tied to, say, a flake, and a fire-cracked rock, but not a groundstone.

How many different attribute types would your DB require?

Depends. While some tables are shared (color, material), each of the artifact items (flake, groundstone) also have their own unique attributes that are not shared. For example, the uses of a groundstone is a completely different table from the uses of a flake. So I find myself making tables like tbl_FLAKE_Use and tbl_GROUNDSTONE_Use.

Lastly, bearing in mind that the processing of each different type of master record would have many similarities (I assume), would it not be a good idea to follow the same approach in the (single) master table too?

Unfortunately, not enough similarities due to the reason above. A flake will have material, color, function, style, etc. while a groundstone will have material, type, completeness, texture, alterations, etc.

PS. What does GIS stand for?

Geographic Information Systems :-) Has to do with mapping and data analysis with respect to a geographic location.

[NeoPa]

I would still be tempted to take the more normalised (See Normalisation and Table structures if you've not done so already) approach. This is a matter of choice and style though. You need to do what suits you best.

Where my attribute table would differ from yours, other than in the basic fact that my one table would replace all of your more particular ones, would be in the extra field to indicate which attribute type it was.

Table Name=[tblAttribute]
 Field      Type   IndexInfo
AttType    Text   Composite-PK
Attribute  Text   Composite-PK
AttDesc    Text

Some typical data to handle colour and size attributes might be :

AttType  Attribute  AttDesc
"Col"    "RD"       "Red"
"Col"    "BU"       "Blue"
"Col"    "GR"       "Green"
"Siz"    "10"       "10 Inches"
"Siz"    "20"       "20 Inches"
"Siz"    "30"       "30 Inches"

[AccessIdiot]

Aha! That makes very good sense. Just make everything text. I like it.

Question about normalizing though - by repeating something like "col" over and over aren't you not fully normalizing? That's why I had everything broken out.

Also, how do you create a combo box in the form for only the "col" attribute? Do you set the control source to be a query where AttType = "col", order by Attribute?

thanks!

[AccessIdiot]

Table Name=[tblAttribute]
 Field      Type   IndexInfo
AttType    Text   Composite-PK
Attribute  Text   Composite-PK
AttDesc    Text

Hey NeoPa could you please explain the composite PK you have here?

[NeoPa]

Aha! That makes very good sense. Just make everything text. I like it.

That's not the fundamental issue here. It could as easily be done with numeric codes. I just think text codes here are more readily understandable.

Question about normalizing though - by repeating something like "col" over and over aren't you not fully normalizing? That's why I had everything broken out.

As far as I understand, no.
Repeating data (where we consider this as separate from the indexing) is proscribed. As part of the index (identification) this is not the case.

Also, how do you create a combo box in the form for only the "col" attribute? Do you set the control source to be a query where AttType = "col", order by Attribute?

That's about the size of it yes.

...
WHERE [AttType]='Col'
ORDER BY [Attribute]

Clearly this enables a DB admin easier access to (and better overview of) the attributes data.

[NeoPa]

Table Name=[tblAttribute]
 Field      Type   IndexInfo
AttType    Text   Composite-PK
Attribute  Text   Composite-PK
AttDesc    Text

Hey NeoPa could you please explain the composite PK you have here?

If I knew what you didn't understand I could ;)

In Table Design view of a table, you set up the fields then, after selecting the two CPK fields you click on the "Primary Key" button. This sets up the two fields - as a consistent pair - as the Primary Key.

Individual entries of each field can be duplicated, but the pair together must be unique.

Let me know if I haven't answerwed your question.

[AccessIdiot]

If I knew what you didn't understand I could ;)

In Table Design view of a table, you set up the fields then, after selecting the two CPK fields you click on the "Primary Key" button. This sets up the two fields - as a consistent pair - as the Primary Key.

Individual entries of each field can be duplicated, but the pair together must be unique.

Let me know if I haven't answerwed your question.

Yeah I guess I don't get what you would suggest using as the composite key (what fields). Usually I take the easy way out and make an autonumber ID field as my primary key. I've only used a composite key when I had a many to many relationship and needed to make a table that contained the primary key of two tables. Like a survey can have many staff members, and a staff member can complete many surveys.

thanks again

[NeoPa]

In a case like this, although you could happily use an AutoNumber field as the PK, you would necessarily have to define a key consisting of both fields ([AttType] & [Attribute]) as unique anyway. It's easier to use that as the PK but not absolutely necessary.

I assume that I've answered your question now. Please let me know if there's anything left that you still need to understand (better).

[AccessIdiot]

thanks, that does it!