Know your data
This is another mistake that I made. My data was purchased from a
data company. This company made the same mistake that I had by not knowing the data. I am
glad that I am not the only one that has made that mistake.
Without getting into exact details, if you want to use a code that will represent
other items use numbers. Real numbers, and no numbers that start with a zero. If they
start with a zero, they have to be stored as character data types. When you have millions
of rows of data it is much quicker to query on numbers then alpha characters. There are
cases where you may need letters as codes, but before you decide, make sure that you know
exactly why you decided the way that you did. It sounds silly but it can save you many
problems. I like using codes in my data storage, especially when I have many records that
will have the same data in a field. For example, in a big address book would you want to
store each county that a person lives in? Or could you use a code? I live in Orange County
and many of the companies that I do business with are here also. Instead of storing the
word Orange, I will store the number 1 in a field for all companies that are in Orange.
Why would I want to store county? The taxes are always changing by what county that you
live in. I would have a table that sits to the side that would contain the County,
County_Code, Tax_Amount, and County code being the unique key.