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

My biggest reason for not type-prefixing column names is it has a tendency to make the process of typing out my queries that much longer when I have to remember (and then type) the prefix of my columns instead of just typing out the logical name (such as FirstName instead of strFirstName).

Answer 3

Typically, you, or those working with your data should know the basis of your data. Since queries aren't truely strong-type enforced, you could parameterize any query. Your first time of testing the query would either work, or fail. Fix it, then move on.

As for working in the visual IDE, I've seen many shops that don't enforce naming the objects (textbox, comboboxes, etc) with a common convention. Since a lot of stuff I have historically done is dynamically generated, linked, bound, I use such prefix on controls, such as txtFirstName, btnOk, cboStateList, etc. Then, the controls, I strip the first 3 char and have the name of the field (if applicable) to auto-bind at run-time to a data object. However, as stated first, a prefix on column names in a table can cause more problems than it helps.

Just my dollar's worth (inflation from 2 cents)

Answer 4

The problem is between the so called Apps Hungarian Notation and the Systems Hungarian Notation. The former adds information about the kind of data you have (e.g. dx could mean "number of pixels from left border"), while the latter adds information about the type of data you have (e.g. bit to mean bit).

With current programming environments and methods, you never have to lookup what type of variable you're working with. The IDE and the compiler will tell you if you're wrong. So this is essentially redundant data, which starts to get into your way when you start (auto)generating source off those names:

// just looks wrong:
public void SetSomething(bool bitPlayerHater)

Read Joel's article about Making Wrong Code Look Wrong. Especially the section "I’m Hungary".

Answer 5

Plus if you ever have to change your data type (it happens more than you think - we just moved to unicode), you have to go change column names all over your code. (that's a bad thing btw!) :-)

Answer 6

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.

Answer 7

In my experience, database systems are pretty good at enforcing the type-safety of operations, so such prefixes are hardly necessary. The database idealist in me says that the system should be based around domains (abstract data types) anyway, so the low-level data type would basically be irrelevant as far as compatibility with different operators is concerned.

Once the system has been implemented, anyone needing to know the type of a column can easily query the data dictionary/system catalog to find this information. At the modelling stage, the types would normally also be readily available as part of the specification.

One good reason for not using the prefixes: it would make it difficult to perform queries on the data dictionary sorted by identifier, since the leading portion of the identifier is now really the type. In my opinion, the type is a distinct property from the identifier, and should therefore be stored separately.

Answer 8

I was about to say that I worked on a Hungarian named database for years... it was mostly OK, but over the years some of the number prefixed fields actually contained alphanumerics, and some of the boolean flags wheren't, and a few of the varchars had become clobs, and so-on and so-on... enough to represent a significant trap for young players anyway.

I didn't and don't think there was actually anything "wrong" with the naming convention... it's just a bit infelxible, which developers can cope with... but I do agree that it adds very little value... programmers are generally pretty smart people, and memorising the datatypes of a db-schema doesn't actually present much of a challenge... especially if you work with a system for an extended period.

So all in all, I'd give Hungarian notation the thumbs down, but if I inherited a system which used it, and I created new tables, I would follow the convention... It's no skin off my nose either way.

If they keep whinging, Send them to me. I'll give them something real to get pissed-off about, like PascalCase in a Java program, or uncapitalized CONSTANTS, just for instance ;-)

As an aside: I've also carried over the VB-standard for naming controls (and controls only) into Java, because it makes sense, and it presents useful information; and it's so widely known and used that it serves as a lingua franca, even if it goes against the Java coding standards.

My ethos on coding standards:

• Do whatever works.
• Read and understand atleast one published standard, but don't follow it dogmatically. See point 1.
• When in Rome... consistency is the key to making it "hang together". See point 1.

Cheers. Keith.