Simple SQLServer Table Design Question

StrataFrame Forum

Simple SQLServer Table Design Question

http://forum.strataframe.net/Topic3662.aspx

By Scott - 10/17/2006

I am not a SQLServer guy. We are moving our current VFP->DBF system to .NET->SQLServer and I have some table degsin questions. In our VFP system we had a transaction code table with a 4 character code key field and a transaction table with the the transaction code (key for the code in the transaction code table) and other transaction specific details. I want to maintain that same design but am hearing arguments that I should use an integer key and have the integer key in the transaction table. Now I realize that integer keys should be faster when doing joins but my logic is that I don't have to even do a join on the table to get the code.

I should mention that the transaction codes are not arbitrary, the values are like "PAY,NOT,NC" and the action of the transaction is determined by these specific code values, so if integer keys are used, any time I want to process a transaction I would have to lookup the code to determine the appropriate action.

I have several other tables that are in a similar type situation, like schedule codes and appointments. So am I wrong here or should I use the character keys in situations like these.

By StrataFrame Team - 10/18/2006

There are people on both fronts of this argument... yes, integer keys join tables faster because of the indexing scheme for integer keys, and quite frankly, they can be easier, because you don't ever have to worry about the collation or anything like that. However, for static data, such as lookup tables, you will find plenty of people that will argue that it is better to use some sort of character relationship between the records because like you said, you don't have to lookup the "lookup" record before you insert it's pk into the fk slot on the new record. Also, when you query the records at a later time, you don't have to do a join at all to the lookup table because the character pk is a human readable data type.

So, I won't tell you what to do because it's your database, but basically, the argument is that using integer PKs is the standard way to normalize a database, but you have to trade normalization for ease of use. And for those that argue speed... if you don't have to do a lookup or a join because you know the pk and the pk is human readable, then you're actually requiring less from SQL Server, so it's certainly going to be faster to use a character pk because there will be fewer queries and joins.

Hope that helps Smile

By Paul Chase - 10/18/2006

Scott,

I use char keys in several situations similar to what you are describing and have not had any issues with using them speed or otherwise. Of course I don't have 20 million records and a website that is getting 10,000 hits a minute. I have almost 2 million recs in a table that has a compound (char(5) + char(10)) primary key and I am happy with the performance.

I guess it would depend on how big your data is and will become and what you mean by performance. You have existing data already so why not set up both scenarios and do some tests to see what the real difference is. To me a few milliseconds or even a second might not be that big a deal but you might think it is the end of the world.

By Scott - 10/18/2006

Thanks for the input. I think I will be using char keys for my lookup tables.

Scott