1) Depends on the data type I would think. Never heard of any rules like "make employee names varchar" - but that would work. It boils down to how you're going to use it. I've seen some fields "name" with first and last name, which robs you of the ability to use mysql to sort by last name, first name.

2)it is absolutely better. Numeric fields take up less space in your database and when fetching rows, and selects on fields will **always** be faster than text searches.

Two general rules of thumb:
- If you have lots and lots of rows with columns that are mostly empty or redundant data, put that data in another table and join on it. In the example below, counties and regions would have row after row if the same data if you stored it in the states table.
- If you can figure out a way to use a numeric type instead of a text type, do it, it will make your queries much faster. This

select employee.fname, states.full_state from employee,states states where employee.state=states.state_id and states.state_id=1234
will always be faster than
select employee.fname, states.full_state from employee,states where employee.state=states.state_id and states.abbrev='AL'

Your state list example is perfect - but don't use the two char field as primary, do something like

id int(11) primary key auto_increment, state_id int(11), index(state_id), abbrev char(2), full_state varchar(50), county int(11), region int(11)

... where county and region have their own tables.

I like to use a unique number for "state_id" that is not the auto_increment field. This is so if the data ever has to move and the table is re-generated, you don't get the wrong id's connected in joins. Example


id|abbrev
1AL
2|AK
3|AB <-- oops, alberta, we want US only, delete
4|AZ
5|AR

So we have

id|abbrev
1AL
2|AK
4|AZ
5|AR

But unless you do a data dump, when populating a new table you'll get
id|abbrev
1AL
2|AK
3|AZ
4|AR

And all records joined on states after id=2 will be off by one id.

rocknbil,

OMG! Thanks so much for your tip on not using the primary key as an ID for records. This is the third database driven web app i've created. While the first two apps don't use the primary key as an ID, this third app does! I did not think of the problems that would be encountered if the database table ever has to be regenerated.

I'm in the middle of creating a web based customer & job tracking system for a client. The mysql database consists of; customer, employee, pricelist, joblist, workorder tables and so on. They all use the primary key as an ID.

I will add a new ID column for each table right away!

Normally this is not a problem since primary keys do not reuse deleted key numbers. Also, the default for exporting a database or tables in phpMyAdmin is to include the Auto Increment (primary key) values.

So my questions are...

1. is not using the primary key as an ID normal practice?

2. what's the probability of this issue occurring?

I don't remember reading about this issue when learning about MySql or relational databases in general, but it's an obvious point!

Here's "Poster Child" example i had just experienced, i just had a problem with table that used the primary key as an ID. I needed to delete an index entry. While phpMyAdmin said i could - i could not delete it, so i exported the table without the auto increment (primary key) values. When i recreated the table, i realized that all the ID's (primary keys) changed in relation to the records.

So the moral if this story is to export tables with auto increment values. Or, as you pointed out, don't use the primary key as an ID.

I have just read that a primary key is also called an "Artificial" primary key because it is an integer number that is system auto-generated, auto-incremented, and maintained by MySQL database engine. It's also preferred that ID's be "Artificial" integer keys, just not the primary one. But doesn't the problem you described have to do with system auto-generated, auto-incremented keys?

If say... the customer ID is another "Artificial" integer key, doesn't regenerating the table pose the same problem with any system auto-generated, auto-incremented keys?

thanks,

Well, it is pretty common to use the auto increment field as the join field, and if you are moving database with an .sql data dump, the primary keys will remain the same. The problem comes in when you attempt to move data by other means. A good example might be, let's create a new email list for a second site. We'll use all the base customer data from table A, just our new mailing list will contain records for who gets emailed, etc.

I usually generate it by one of two means, get the highest record id of the join field and increment it, or just put the current auto increment value in the join id field. That way it never changes.

ok, thanks! great tips.