Database columns type prefix

Question

I’ve been developing solutions with databases for more than 11 years now, and it seems I’ve “developed” a rather controversial opinion about naming columns in my tables: I always give them a 3 or 4 character type prefix, i.e. intGroupID, nvcTitle, dtmCreated, bitPlayerHater, etc. I’ve worked with several other developers who all absolutely despised the old-school prefix convention.

(yeah, I know, I didn’t invent anything here, I’m just refusing to give it up:)

My primary reasoning is to provide as much information as possible to my fellow developers when they attempt to understand the structure of the data. Knowing the type of the columns instantly gives you (or me, at least) a better mental image of what you’re dealing with. And you usually don’t have the same intellisense support from the IDE when you’re writing queries compared working with C# or VB.NET.

So far nobody has been able to come up with the killer argument that could change my mind on this particular topic. I have a couple of other equally controversial naming conventions which increases clarity, but the column prefix seems to piss more people off.

Why is prefixing database columns considered such a bad practice?

Answer 1

It's called "Hungarian Notation".

As a developer (and data architect), I find it worthless. It doesn't provide much information.

1. It only provides a quick, inaccurate gloss on part of the type information. It omits length, for example.

   In more complex database environments, where the objects are BLOB's, it provides no information at all about the type of object in the blob.

2. It makes changing data type painful.

3. It's necessary to remember an obscure prefix. Is it vcName, strName or uniName?

4. SQL handles type conversions automatically, making fussy type-specific naming largely irrelevant.

5. Most Important: It provides no useful documentation on the meaning of the data. My experience is that people corrupt the meaning almost always. They're rarely (if ever) confused on whether it's int or string; and when they want to know, they simply describe the table using TOAD or some other tool that gives the ACTUAL type, not a partial summary of the intended type.

[Having said that it's approximately useless, I realize that this is probably not the "killer argument" you're looking for. It would help if you could update your question with the actual reasons, point-by-point, that you feel are the value of Hungarian Notation, so they can be addressed point by point.]

Answer 2

I have changed column types a few times. For example a code from number to string. Without prefixes I can do it without recoding n most cases and all applications will still run (at least in oracle). With your method I need to change intCode to strCode and I'm 100% sure I need to redo all my code using this field!

The same is true for changing integer to floats.

Some people also prefix column names with the table abbreviation (e.g. department.dep_code). I find that really hard to code with as I tend to forget the abbreviations. A system with 50 tables tend to get very similar prefixes. A reason for using it is when joining employee with department, department code field is a unique field (emp_code and dep_code). I do not think that it add value as this can be done easier using table aliases which do not force you to use prefixed.

I used prefixes in my client code a lot for GUI components. E.g. sle for single line edit but also the hungarian notation for c and powerbuilder. When moving to java I stopped using it. I guess I used clearer names for my variables. However moving to an object oriented languages is, is that everything is an object and it is a bit silly to use objVariable everywhere.