----- Original Message ----- From: "Stuart Felenstein" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 25, 2004 3:36 PM Subject: Could this be dangerous :Values and Labels
> Or maybe just bad practice. Thought before I go any > further I'll ask. > > I have a few static tables that list out "items" and > the primary key is an "assigned" ID. Meaning I did > not set auto-increment. As I add items I will add the > associated ID number. > > Now to the transactional tables. While the label has > the item listed in the form, it gets inserted into the > table with the ID number. To illustrate: > > Static_Table > ID Value > 1 United > 2 Jet Blue > 3 Southwest > 4 American > > Dynamic_Table > MemberID Airline_Pref > 200 1 > 201 4 > 202 3 > 203 4 > 204 1 > > Pros cons dangers advantages comments ? > I *think* you're asking if it is okay to use codes instead of real values in databases, such as '1' for 'United'. That is more than okay, it is very widely done. The pros are pretty obvious: - codes are usually much shorter than the real values, which results in space savings for data storage The cons are a little less obvious: - unless you memorize the codes (which you *will* tend to do over time, as long as there aren't too many of them), you will have to do lookups to determine what real value corresponds to a given code. That often translates into extra joins in your programs and queries. This should not be a big deal though since joins usually perform pretty well in most cases. I'd like to offer one comment. If I were making up the codes, I'd try to choose codes that were mnemonic, such as 'U' for 'United' and 'A' for 'American'. (Or maybe 'UA' and 'AA' since those abbreviations might be self-explanatory to many users of your system.) Lastly, with regards to Auto-Increment, you should not feel that all keys should be Auto-Incremented. I think of Auto-Increment as a convenience for generating key values when you don't have any strong preference for a given key having a given value. However, I don't think you should ALWAYS use Auto-Incremented keys. For example, your airline lookup table is more meaningful if you use short letter codes for your airlines than if you use integers. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
