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…
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?
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.