Normalization Question?

[sprtrmp]

Good morning, I have a theoretical question regarding (what I feel is a) normalization issue: Bear with me, and I will try to describe what is going on.



I have a table (tblCrewmembers) that stores data related to aviation-related personnel (pilots, etc.). One of the fields on table stores a 3-letter abbreviation for each person's military rank (SGT, CPT, GEN, etc.) the way I have this set up is that all of the possible ranks are on a separate table (tblRank) with 2 fields ("RankID_PK", which is just a unique auto-number, and another field called "Rank", which holds the 3-letter abbreviations for each of the ranks (if anyone cares, there are 27 different ranks.)).

When inputting information on a new crewmember, for example, there is a form that has a combo-box that uses tblRank as it's Rowsource that the user uses to select and store the "RankID_PK" for that person'r rank to tblCrewmembers, rather than the actual 3-letter abbreviation. the reason I did it this way is for all of the normal reasons related to everything I have ever learned about data normalization. ie: don't store the same data over and over again, if you can store a single character that REPRESENTS that thing, blah, blah, blah.

So, when i built the table, i set it up that way, to where the combo-box refers to a separate table, rather than a hard-coded list as a rowsource. NOW it is causing me some trouble because i am trying to "auto-populate" a completely different form with a person's rank and am not able to do it easily because the data that is stored is an auto number, rather than the actual rank and i am nowhere near good enough at SQL or whatever else to get the info the way i need it.

My question is: is it REALLY such a terrible waste of resources to store a 3-letter abbreviation over and over again, if it makes it easier to code things that i need to do later on?
more than anything else, i want to build a good product, so i am wanting to know hat the HONEST best way to do it is. i am just about at my wit's end tryng to get it to do what i need to do and I recognize that it would be easier to use the abrreviations as opposed to autonumbers, but doing it that way goes against normalization rules as i understand them.

Thanks

[pbaldy]

I certainly believe in normalization but I'm also pragmatic. In the situation you describe, I'd have tblRank with a single field, the 3-letter code.

[pbaldy]

I should have added, I might have a second field with the full text, like "Captain", if there may be times you'd want that.

[sprtrmp]

Jeez, there is an idea that NEVER would have occurred to me... So, really there is no NEED for the auto-number field??
in that case though, why have a separate table at all? isn't the point to just save the single character-that represents the text-instead of the text (or whatever it would be?) itself?
Why not just add the value list to the rowsource of the combo-box?

(Man, years later, I am still wishing I had skipped that meeting where the Colonel pointed at me and told everyone that I was gonna be the "database guy")

[pbaldy]

I'd use the table for combo row sources, so you can use limit to list and avoid "CPN", "CAP", "CPT" etc. You can use a value list, but then you have to repeat it on any form you want a combo on. A table is also more maintainable, though in your case the selections are unlikely to change.

[ssanfu]

My $0.02 worth......

Why not just add the value list to the row source of the combo-box?

You could do that. Or you could leave the list in the table (without a PK field) and use a query in the combo box.

If you use the value list, the list should be very stable (non changing).

Lets say you are using the table with a autonumber PK field. You get a new Colonel that orders 4 characters instead of 3. Change the table values and you are done. The change to 4 characters appears in forms and reports - no update queries to execute.

With the value list, you have to edit every control that uses the value list. Then you have to run update queries to update the field to the new 4 characters.

Personally, I always have an autonumber PK field. I don't use value lists because if I want to add something new (say the full rank) it is easier to add a field to a table than editing value lists through out the dB.

[sprtrmp]

OK, I definitely see what you are both saying. I will most likely change it to a table with a single field, just to make data retrieval easier later on down the road (and because as of right now, the "Rank" table is used in 3 places and would be easier to update than 3 value lists). In this case, I am 100% sure that the 3 letter abbreviations will be in use long after I am gone. the same 3 letters have been used since before I came along and will continue to be. no one likes to change anything in the army.

The thing I was really having difficulty with was that I have read very little about databases and haven't been able to study them as much as I really NEED to, but one of the few things I have read was something along the lines of "...never store the same thing over and over again" so, I thought I was following one of the hard and fast rules of databases by following the PK/FK setup with the ID number stored instead of the actual data. BUT, it looks like I am wrong once again: the people who actually know this stuff don't seem to think its a huge deal.

[ssanfu]

BUT, it looks like I am wrong once again

Nope, not wrong. I saw a quote a long time ago: "Normalize til it hurts, de-normalize til it works...."

As Paul said, your data looks to be very stable. So a table without a PK field is OK.

I would use a table with 3 fields
RankID_PK
RankAbbrv
Rank

BUT, that is the way like I design tables.


The great thing is, both designs are correct.

[NightWalker]

So what you guys are saying is if I want a look up table instead of a value list I can have a one field table with the list of items for my cbo's? Also that table would have NO PK?

This normalization is making my hair turn grey. I keep normalizing my tables down too far and then have blank cells in my records. I know that is not correct also. I just wish I could get this normalization part down before my hair starts falling out. LOL!!!

[pbaldy]

I would probably only do it when the selections were fairly static and it was only the one field that would be looked up anyway. Where you're basically providing a list of options to choose from. In most circumstances, there are other fields, and like Steve I'd always have a numeric key field in those cases.

[sprtrmp]

Nightwalker,

Data normalization is only just staring to make a little bit of sense to me. The biggest issue I am facing right now is knowing when to STOP normalizing data. It is actually possible to break data down too far.

I did try to go back and eliminate the numeric key field, but ultimately decided to keep it because with out it, I wind up with a list of ranks in alpha-numeric order, opposed to "rank-order". So, I am going to keep the numeric field for ordering purposes and change the bound column in the combo box to store the rank abbreviation. Close enough.

But, ultimately this was a beneficial post, as I definitely learned a thing or two.
Thanks to everyone for the input!!

[ButtonMoon]

I recognize that it would be easier to use the abrreviations as opposed to autonumbers, but doing it that way goes against normalization rules as i understand them.

You misunderstand normalization. Normalization has nothing to do with using numbers (much less autonumbers) in place of strings. Normalization is all about ensuring dependencies are enforced by keys in your tables. If it makes sense to make the abbreviation or any other string a key then you should do so. Normalization requires you to do that whenever you have functional dependencies on those attributes.

I don't agree with the suggestion here that you should make a table without a key. Every table ought to have a key.