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:
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…