General Table Design Question 1

[Auguy]

When you need simple indicators in your tables for things like "Order Type" where each record is always going to have a value and there are very few unique values (3-10) and the column size is 1-4 characters, is it better to have the column be an integer or tiny integer? Then have other tables with a "Type" column and an integer PK column to use in the main table. An example might be something like "W" for wholesale, "R" for retail, "I" for inter company. I realize if I need to change the values I have to go back and change all of the old records, but for me this rarely happens. Just wondering if the integer columns would make for faster and better selects, etc. Most of the tables I work with have much less than a million records and are confined to one server. I think most of you will recommend the integer approach to reduce the need to ever change old records. But is there a place for using the small character fields?

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

Here's my opinion....

There will not be much difference from a performance perspective. My answer would likely be different if you had billions of rows instead of millions. If you are using a Char(1) for status, it will take exactly 1 byte per row. A tiny int (valid values between 0 and 255) will also take exactly 1 byte per row. If you use a Tiny Int, w could be 1, R could be 2, I could be "inter company" etc.

My advice would be different if your "code" was 10 or 15 characters long, but for a single character or 2, there's not much of a performance benefit.

Now let's think about data quality. If you have a char(1) column and the only acceptable values are W, R and I, then I would encourage you to put a constraint on the column limiting it to those individual values. This will prevent bad data from getting in to the database.

If you decide to go with the Tiny Int column, I would recommend a corresponding lookup table that has a primary key in the lookup table and a foreign key in the data table. The foreign key will prevent you from getting dirty data much like the constraint works.

The difference is this... suppose you wanted to add another code (like G for government). If you use the constraint method, you would need to modify it to accommodate the G. If you use the lookup table method, you simply need to add another row to the lookup table.

Personally, I would go with the lookup table approach because it gives you the same performance, the ability to store long descriptive names in the lookup table, and also makes it easier to add values later.

My 2 cents.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George, that's what I was expecting. Always good to read your posts.

Auguy
Sylvania/Toledo Ohio