Should "lookup fields" be strings, ints, what?

[gdrenfrew]

In development, we have a couple of databases each similar, but each being designed by different developers.

Lookup Table on Dev
1 Approved
2 Rejected
3 Deleted
4 Cancelled

Lookup Table on Live
1 Approved
2 Rejected
3 Deleted
5 Cancelled


Without fail, someone will write a query based on the ID field of a look-up table. Then when that query is ported to live, doesn't look-up the same value and fails/returns wonky data.

So some developers build their queries by joining on the string value.

To me this seems pretty inefficient, or is it not a problem? I get unduly riled by this lack of a level playing field amongst us.

Is there a best-practice?

 

[SQLDenis]

either sync the tables up or get the id by searching for the description

select * from table1 t1 join table2 t2 on t1.id =t2.id
where t2,description ='Deleted'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

I normally use the ID(Int, Identity) column for look ups, however, there are times when I use the Desc. column. Just I would assume you want the Desc to be unique, so make sure you create a Unique Index on it.

Jim