I have a fellow developer advocating storing text rather than an ID. For example:

I have a lookup table for a persons title, it has 2 columns:

TitleID as integer (primary key)
TitleDescription as varchar

I would normally store the TitleID in the table that references it, which i would regard as standard practice

He is saying i shouldnt store the ID but store the TitleDescription and that i wouldnt need to have an ID

His argument is that it will perform better as there is no joins when querying

Me and the fellow developers do not agree that this is a good design practice. I would appreciate your thoughts on this.

Thanks

Richard

If you have surrogate keys already established, why would you not use them?
I say store the ID.

Rudy will probably chime in now and say store the natural key.

So there you have your answer.

It's really going to depend on a few factors. Are the text/varchar values relatively short (as compared to say a short story)? And do they change at all often? If these things are changing on a monthly basis, then definitely go with the Surrogate key. If you are looking at US State codes stored in a table with millions of rows, I would go with storing the character data in the million row table.

sorry, i was out all morning, otherwise i woulda chimed in sooner

if you don't already have the surrogate keys established, your developer friend is right

however, you should nevertheless still declare the Titles table for relational integrity porpoises!!!!!!!!!!1!!

and you dasn't has to use a numeric id for it

Assuming by title you mean Mr, Mrs, Miss, Dr etc then I'd go with storing the data as a simple character field. My reasoning would be:

• Why complicate the data for no real advantage?
• The data will be easier to read.
• It will be just as fast as using an id, if not faster as you now don't need to join to another table.
• The SQL code will be marginally simpler as fewer joins required. It doesn't sound like much but these savings soon add up across a whole system.
• It uses just the same amount of space though who really worries about saving a few bytes space these days?

It hardly seems a major point to arguing about though.

Mike

EDIT: though, as mentioned elsewhere, if you're already using the id field then you should continue using it.

There is no single best answer. Surrogate keys might save you some space in some cases but at the cost of having an extra index to update and more joins in your queries. So weigh up those kinds of factors and determine what is most beneficial.

What's bad in my opinion is any blanket assumption that a surrogate key always should or always should not be used.

Well I say his friend is wrong. Nyah.

Thanks for all your replies. I must admit i have used surrogate keys as this is the way i have always done it. I have now researched Surrogate keys vs natural keys and from what i have read both are correct in the right situation. The issue i have is that some of the items can be quite long >50 letters and these items have nationally defined coded values. Also we have to import and export these values as the nationally defined codes. The likelihood of the lookup description changing is high. We also have lists defined by the users of the system and often they are misspelled, so correcting the data is easily corrected if it has a surrogate key. So i am still thinking surrogate keys are correct for my lookup lists.

Ah yes - DKNF!

pootle flump ur codings are working excelent.

Agree with you re surrogates then, although the bit in bold suggests a semi-intelligent key?

pootle flump ur codings are working excelent.

So why not use the codes as a key? Assuming the code is a manageable size that sounds like a good plan. A nationally defined code is not a surrogate though.

I prefer using surrogate keys myself. But I think the important thing is to be consistent. I think it is poor style to use surrogate keys in some tables, and natural keys in others. To me, that is kind of like using multiple naming conventions.

that's just plain ridiculous

i have a very strong suspicion that you said it only to be a nasty, evil troll

stop it, just stop it



"A foolish consistency is the hobgoblin of little minds,
adored by little statesmen and petty DBAs of all kinds"
-- apologies to Ralph Waldo Emerson

you would srsly use a surrogate key instead of the natural state code key in your states table? instead of the natural ISBN key in your books table? instead of the natural VIN key in your vehicles table?

Do you really value "style" more than practicality and performance? I don't think "style" is a good criteria for choosing keys. Naming conventions are a very different matter because they are purely concerned with presentation and comprehension. If a naming convention could have an adverse impact on performance or maintainability then it would be a good thing to break that convention.

Them's fightin' words, you worthless piece of leder-hosen clad moose meat. I'll meet you halfway to settle this....which would be what? Wisconsin? Where the heck are you up there anyway? It's all like one big State to us. Like, the downstairs of your house has different rooms, but the attic is just one big space you occasionally have to visit when the roof is leaking...

Take that!