Too Many Indexes

[Stingaway]

Wouldn't be able to just run query from that table to see this specific information?

To paraphrase the great theologian Han Solo, "Navigating relational databases ain't like using Excel spreadsheets, boy..." and this is one of those times when it's going to be a LOT better to listen to Obi-Wan... trust me.

Bob is spot on with his analysis of your database.

It's not personal - it's data. Cold, hard, unfeeling, factual data. Just like I like it.

If you are ever planning to do MORE with the data than just make it look pretty on a form, you are going to want to look into data normalization and the reasons behind the scenes as to why relational databases work the way they do.

"Just running a query" isn't going to be the issue later. It's going to be 'how do I put more data into the table now that the poor table design throws errors like 'too many fields', etc."

[boblarson]

Wouldn't be able to just run query from that table to see this specific information?

Are you talking about my question as to will you want to see "Which States a Rep is 'RA' in ?" Or the other one? If so, then NO, you wouldn't be able to just run a query to see that specific information. You would not be able to get the counts easily, or compare others, or have a list.

[RayMilhon]

The short answer to your question is probably. However, Bob is correct in his assessments. which is why I asked my original question. Your design may be fine for you now but future enhancements, Reports, maintenance will be that much more difficult. Especially as requirements change. Using a normalized database will make things easier on you.

[cbrsix]

The correct table structure would be

tblStateReps
StateRepID - Autonumber(PK)
StateID - Long Integer (FK)
RepID - LongInteger(FK)
RepTypeID - LongInteger (FK)

tblRepTypes
RepTypeID - Autonumber (PK)
RepType - Text

And then you would have a record for each rep for each state.

Can you help me understand why I would need all of these foreign keys. First, I only have 1 Reptype. The AG/RA refers to their registration status in a specific state. Does this make your second table unnecessary? If not, why? Also, what's the difference between he StateRep ID and StateID? Are you suggesting that I create a table that just lists all the states to create a stateID?

[boblarson]

The AG/RA refers to their registration status in a specific state.

As long as you know that there will NEVER be another type, you could just enter the test instead, but I hesitate to say never and instead set it up so it is way more flexible should something change.

Also, what's the difference between he StateRep ID and StateID? Are you suggesting that I create a table that just lists all the states to create a stateID?

State Rep ID is would just be an ID for each rep which can then be used as a foreign key in any other table that might pop up as necessary down the line. StateID is having a states table and then you store the ID number which then is more efficient than text for indexing. I usually will create a states table (actually I've only created one and just import it into any database I need).

[cbrsix]

State Rep ID is would just be an ID for each rep which can then be used as a foreign key in any other table that might pop up as necessary down the line.

You also state to have a RepID in addition to the StateRepID, which in your description, seem like they would be the same thing. Is this a correct assumption?

[boblarson]

You also state to have a RepID in addition to the StateRepID, which in your description, seem like they would be the same thing. Is this a correct assumption?

Oops, that was a typo. RepID wasn't supposed to be there as I had typed it and intended to change IT to RepTypeID, not RepID. So I ended up adding that RepTypeID instead of changing that one line and forgot to delete RepID.

[cbrsix]

Ok. Thank you for all your help. It seems to be working so far.