An integer column can hold rather large numbers, why not go ahead and use the ones that are <100? They are as valid as any other number, aren't they?
I think you are confusing how things "look" with what they "are". If you need really BIG numbers so that you can identify MANY rows of data use a "bigint unsigned" column (from 0 to 18446744073709551615) . If you only need to use the values from 0 to 255, use a "tinyint unsigned" column. Read - > http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html You generally don't have to make your keys pretty, just make them work. You make them pretty when you show them to the user, the database doesn't care. Regards, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein <[EMAIL PROTECTED]> wrote on 08/25/2004 04:41:28 PM: > The other idea I had aside from mnemonic (that was > yours) was to use beefier numbers. I started listing > them from 1. I am thinking of 3 digits maybe. > > Stuart > --- Rhino <[EMAIL PROTECTED]> wrote: > > > > > ----- 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] > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
