rocknbil - 3:47 pm on Aug 26, 2011 (gmt 0)

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.