To Prefix, or Not to Prefix

Today I had a little “tiff” with a developer about something very trivial by most accounts. Maybe I was having an off-hormone day, or my biorhythms are off.

A simple table was proposed to hold the text name behind a status property. Something as simple as:

ID STATE
1 DRAFT
2 REFERRED
n STATEn

The developer suggested “status_description” as a field name. That was all it took to chew up 25 minutes.

The IRC conversation goes something like this:

11:16 <jon> what do you propose for state name?

11:16 <sam> status_description

11:17 <jon> that’s too long of a field name

11:17 <sam> status_name then

11:17 <jon> i want to say s.name

11:17 <jon> no status_

11:18 <jon> i hate that 😉

11:18 <sam> nope, no go homey

11:18 <jon> the table tells me what it is

11:18 <sam> name is too generic

11:18 <sam> you’ll have 50 tables with “name” field

11:18 <jon> but how can it ever appear out of context?

11:18 <jon> i love 50 tables with name, it forces table aliases and context (like classes and properties)

11:21 <jon> if you repeat the table name on every field… it makes for more fun typing things like that over and over when you are writing sql

11:23 <mac> we do have xxx_id everywhere… for the primary key field

11:23 <mac> i hate it, but it *is* consistent. however, status_name is not necessary, i agree, jon

11:24 <jon> i abhor useless prefixes, it leads to lousy coding, makes the human the compiler

11:24 <jon> but that’s just me

11:25 <jon> i’m going to hibernate for a while, head back to doing C coding and structured crap so i stop thinking in OO

11:27 <sam> not that it matters but prefix has been an Oracle paradigm

11:27 <jon> it doesn’t matter. microsoft used “m_” prefixes on class members (IIRC) and i hated that too. just because a BIG company does something doesn’t mean it is a good idea. IMHO, it is a horrid paradigm to add extra stuff on everything

11:27 <sam> not really, it’s just preference

11:28 <jon> yeah, and my preference is for clean code 😉

11:28 <sam> btw: the object model doesn’t have to match the sql model

11:28 <mac> but prefixes make the SQL read poorly and is extra typing with no benefit

11:28 <sam> for instance the underscore thing I hate with oracle

11:28 <jon> why on the table Person would i want to see every field prefixed? For example, person_first, person_last, person_birthday…

11:28 <jon> of course the FIELDS IN PERSON ARE FOR PERSON

11:29 <jon> who would think otherwise if they are staring at columns like First, Last, Birthday in a table (or class) called Person?

11:29 <mac> where q.section_id = s.id is fine. i preferred “q.fk_section = s.id”

11:29 <sam> just the common id and name… that could be on 100 tables. name is very generic

11:29 <jon> yea, the beauty of tables… THEY PROVIDE CONTEXT to things like id and name

11:29 <sam> you don’t have to prefix though

11:29 <mac> p2_question.name if you avoid aliases for some reason.

11:30 <sam> it’s a DBA thing guys

11:30 <jon> Really? it is better practice, imho, to have context via table alias not prefixes. Maybe I don’t always buy all DBA rules

11:30 <jon> if there was only id,name, across 5 tables, you can’t make a mistake because you HAVE to put it in context (i.e., add a table alias)

11:31 <sam> guys this helps

11:31 <sam> what happens when I create a datawarehouse with a bunch of “name” fields

11:32 <sam> it kind of becomes a redesign effort where prefixes are then needed regardless

11:32 <sam> make sense?

11:32 <mac> use good aliases in your queries?

11:33 <jon> no, it makes *no* sense to me… when u create a datawarehouse you name things properly instead of demanding prefixes.

11:33 <jon> but don’t screw up every table with prefixes for all other use for the someday nicety to not have to make the datawarehouse column names clear

11:34 <jon> why pay the penalty in all coding all the time? please don’t spread the pain 🙂

11:34 <jon> i like to keep the crap in a small part of the app

11:34 <jon> if the dw has to have field names like person_name, agency_name… so be it

11:35 <jon> but i’d rather everybody did this consistently with no prefixes

11:36 <sam> Jon, there are countless whitepapers on proper db design, one google yields this

11:36 <sam> http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

11:36 <sam> speaks to my concern

11:37 <jon> gee where are the prefixes?

11:37 <jon> NOWHERE

11:37 <sam> CustomerName

11:37 <sam> don’t see that?

11:37 <jon> That’s one tiny example in a sea of non-examples. Not to mention this:

11:39 <jon> “Along these same lines, resist the temptation to include “metadata” in an object’s name. A name such as tblCustomer or colVarcharAddress might seem useful from a development perspective, but to the end user it is just confusing. As a developer, you should rely on being able to determine that a table name is a table name by context in the code or tool, and present to the users clear, simple, descriptive names, such as Customer and Address”

11:40 <jon> FWIW you will never sway me to the dark side of name prefixes for everything 😐

I had to go outside and toss the Jolly Ball in the snow, wind, and 5 deg F (wind chill), with my Yorkie…

2 thoughts on “To Prefix, or Not to Prefix”

  1. I think those goes even further back to whether or not use hungarian notation. Its preference until you consider the statement ”

    11:31 what happens when I create a data warehouse with a bunch of “name” fields”

    Now you have to take your data model and implement something else based on it so your naming conventions turn in to a portability and maintainability issue.

    Granted that Jon’s follow-on has merit as well but how much of your initial design thought was about building a data warehouse. If you say you did think about it and that your database is normalized then you ignored what you thought about.

    All I am saying is that you should think about the whole solution and how you can make it easier to maintain and such. The table should describe the context of the data it contains.

    Naming conventions are only preference if the code/model won’t be used by somebody else in some other way.

    John Lamb

    Sr Principal Software Engineer

    L-3 Communications

    PS-If everybody is concerned about data warehousing then why can I only get a six month history from my Bank of America account?

  2. Just caught up with your blog Jon. I think this shows a gap in IRC that we could have worked out via phone. But I’m flattered you blogged about it!

    I always take into account warehouse when it comes to designing a transactional database, whether I’m engaged to build out the warehouse or not. I have worked on DBs that have just a “name” and “id” field or worse worked on pre existing code and these two badboys can make your life troublesome.

    What makes it difficult with id fields is specifically foreign keys. For readability and understanding in ERD models it is poor practice to have a different named fkey than the primary. You simply can’t allow for that if each surrogate key utilized for normalization is labeled id. Sorry if I fired you up there. Working toward a common goal 8-); quality product. Always enjoy the discussion.

Comments are closed.