Column Naming Conventions (rears it''s ugly head again :)
-
Ok,
I know this topic has been beaten to death, but I am struggling with some issues around column naming in our database design/implementation.
Now, I've read LOTS of articles/forums (every one on SSC!) and a lot out there on the web via various google searches. Normally I would what go with what appears to be the Microsoft route on column naming (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconcodingtechniques.asp), and I agree with many of other "standards" out there (Celko, etc).
However, I have a dilemma with the DB that I am currently reworking the design on. I actually (coming from an OO design perspective) personally believe in working towards actually having a column name be unique not only within a table (required of course!), but also in the database being designed. Due to the environment / business process being modeled (Clinical/health care) there are A LOT of column names that end up being named the same thing and are essentially are the same thing, but must exist in each table as they are "visit records" of the electronic clinical form being completed for the patient (I.e. can’t be normalized out, etc... just to nip that thought process in the bud!)
I.e. Almost every form has a section of patient care orders (PCO) that really can't be named something more unique.
Examples:
- InitialVSordered (Initial vital signs ordered)
- InitialVSdetails (Initial vital signs details)
- ChestXray (obvious!)
- IVAccessOrdered (Is IV access ordered for this patient?)
And so on... the only way I can think to solve this is to prefix these column names with a "standard" tablename abbreviation.
- tableNameAbrInitialVSordered (Initial vital signs ordered)
- tableNameAbrInitialVSdetails (Initial vital signs details)
- tableNameAbrChestXray (obvious!)
- tableNameAbrIVAccessOrdered (Is IV access ordered for this patient?)
But, then do I only do it on those columns, or do I now basically commit to doing it on EVERY column in that table in order to maintain consistency!? And... boy does it make for some LONG column names. The longest I think I have so far is maybe 55, but even that seems too long for me. But I also believe that the name should be descriptive and medical terms/descriptions/drug names/etc are rarely short!
Do you see my quandary? Especially b/c it goes again my belief that table names have no place in column names.
I hope I have explained this clearly enough and that someone out there has some insight or suggestions on how to deal with this situation in a "more elegant" fashion.
TIA.
Cheers,
Michelle/Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
I'd leave the table names out. As you write your SQL code, always prefix each column name with TableName.ColumnName. If you're using variables outside of SQL Columns use @TableNameColumnName rather than just the column name. That way, all your code will include the table and column names except for the table definitions themselves.
-Ray
-
I agree with Ray's basic idea but, just a thought... table names, especially when they are large, can make code pretty well unreadable. I usually use 1, 2, or 3 letter table aliases.
The only exception to Ray's suggestion of leaving the table names off the columns, is for an ID column... if you are using a column that most would call an "ID" column, consider leaving using tablenameID as the name of the column. That way, when you use some of the tools in Enterprise Manager and other packages, "join" lines or "key" lines will be formed automatically make it real simple to see what connects to what even in the absence of any keys.
Yeah, I know none of that stuff is supposed to happen but I work with other people...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree here for the sake of yur question. Since you are not changing the strucutre itself and since these columns should obviously not be joining multiple tables and since it looks like most situations except for reporting would not include these columns except from one table I would not fret. Base concept is that they are as unique as they need to be in name and would actualy be better if you could normalize a bit further but you already state that is not on your options.
-
I think that Jeff is on the right track. Using Ray's suggetion makes everyone's life easy. The purpose of a naming convention is to make it code understandable to humans (computers don't care
). If you always use the TableName.ColName in your SQL those who follow will have no problem understanding your code. As I hate to type and long col names are a major pain I use Jeff's TLA during development and then use a global replace to turn the TLA into the full Table Name for production.HTH
Mike
-
Hi Michael,
Doesn't having the full table name on columns make the code a bit difficult to read for troubleshooting? I guess it would be ok if the table names were short like "Customer" but some of the table names our 3rd party vendors use are huge (more than 20 characters and sometimes as large as 40!
). Yeah, I know... get another vendor, but we all know how that goes.
Anyway, thanks for the feedback... always interested in the conventions of others.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, it depends on the type of trouble you want to shoot.
A table name such as "BrilliantUniqueLongLastingSuperHandyIndividualTable" could be confusing and transforming the table name to a 7 letter acronym may not be wise. In some cases a descriptive alias is better than the full table + column naming convention.
Table and column names should eliminate ambiguity. IMHO SELECT Customer.LastName reads better than c.LastName or just LastName.
Perhaps my original post was not clear. I use your system of 1,2 or 3 letter acronyms or abbreviations when writing code (I hate to type) but then once the logic is proven I change the code to use the table name rather than an alas (if possible) and the table name is not ambiguous.
Mike
-
Roger that... thanks. I'm just the opposite... I leave the aliases just for brevity... I figure that it takes about 2 seconds to figure out that "c" means "Customer". But I do like your idea alot... I can definitely see where that would come in handy... Thanks again.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Using table name in column name does not make any sence.
If you want to which table this column belongs to you can write TableName.ColumnName instead of TableNameColumnName.
First option makes optimizer work faster and allows to use aliases.
_____________
Code for TallyGenerator -
>First option makes optimizer work faster
Sergiy,
Can you show me where this is written? The reason I ask is because I've got a bunch of folks at work that use the TableNameColumnName form instead of using alias.ColumnName and I'd like to change their minds. If I could show them, in print, a speed related reason for not using the TableNameColumnName form, it might swing them over to "my side".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I don't remember now where I've read it. It was not only source.
But I know that optimizer takes column name from query and searches all tables mentioned in the query for a column with that name.
It's tricky especially when there are nested queries.
If there is table name or alias qualified this part of job is being skipped. Because you just point exactly the table this column belongs to.
Usually it's not a big deal, but if you mention table name anyway just put dot between table and column names.
_____________
Code for TallyGenerator -
Ray (& everyone) thanks for all the responses so far!
I would of course rather leave the table names out, but there are other "issues" at play here. If I could trust that the SQL code would always be handled via SPs, etc I would have no problem with going the TableName.ColumnName [which is what I always do anyways.. and would still do even if I decide (after all this!) to put some sort of abr. on the column names]. In fact, I've tended to fully qualify with DBname.TableName.ColumnName in a lot of cases in the past.
Unfortunately, I just realized that the way the current front end of the app is designed (or not "designed" really at all in this case) the guy I work with wants to keep some of the current SQL select statements in the ASP pages. He is thinking that it will be "easier", even though I have explained 100x that SPs are the way to go. He is worrried about timeframes and workload on me, as he can (or thinks he can) do the selects in the ASP, but not the SPs. It is totally insane of course to do it this way, especially since one of us is going to have to change all the SQL code anyway b/c I've totally re-designed the DB to normalize, etc.
So, I will re-read all the comments so far and see what I decide I can do with it... if it wasn't for this possibility of him keeping the SQL code in the ASP I wouldn't worry about the "un-uniqueness" of each column name... but be damned if I will let him put DBname.TableName.ColumnName or even TableName.ColumnName in the ASP page. On a side note, it is an internal app only, but I'm still freaking a bit about the security issues (or lack thereof) that may be presented with what he wants to do. I am still working on him on it, but time is the main issue. I.e. He agreed to a roll-out date with confirming with me!
Oh.. Ray... I'm not sure what you mean by "If you're using variables outside of SQL Columns use @TableNameColumnName rather than just the column name". Can you clarify a bit more or direct me to an example?
Thanks!
Cheers,
Michelle/Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
"The only exception to Ray's suggestion of leaving the table names off the columns, is for an ID column... if you are using a column that most would call an "ID" column, consider leaving using tablenameID as the name of the column. That way, when you use some of the tools in Enterprise Manager and other packages, "join" lines or "key" lines will be formed automatically make it real simple to see what connects to what even in the absence of any keys."
Jeff,
Absolutely, without a doubt I do my IDs this way... I also have essentially used the table name in other situations where it makes sense, I.e. ClinicalSite table: ClinicalSiteID, ClinicalSiteCode, ClinicalSiteAddress.
In a table like ClinicalSite, it just sounds right to call it ClinicalSiteCode instead of just Code. It is just these other "weird" repeated, but not possible to normalize column names in the "forms" tables that have me in doubt as to how to handle them. Everything else in these tables makes sense, but I know for a fact that there will be anywhere from 5-12 columns that will have effectively the same name in every table.
I think the main thing that comes up for me in this case is that most people don't know or understand the business side of things in a clinical/medical environment and how things that often work elsewhere, can't or don't work well here. I.e. each of these tables is effectively storing an instance of a patient record/order form. If 10 forms are filled out for the same patient, each different record inserted will have in a lot of what might seem to be "repeated" data, but it is unique to the condition/situation/timeframe that that form is being completed for. It is very difficult to describe, but hopefully that helps people understand a bit better some of the reasoning behind my struggle with deciding how to handle this situation.
Thanks.
Michelle/Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
Perhaps the following will help others understand the complexity of your problem. Note these observations apply to US health care providers and may not be applicable to other countries.
HTH
Mike
Patient is a unique individual who receives the services of a health care provider
Procedures are unique actions performed by a provider. In the US these procedures are identified by CPT (common procedure terminology) codes that are maintained by the AMA (American Medical Association). CPT Codes describe medical or psychiatric procedures performed by physicians and other health providers. For example a tissue culture for non-neoplastic disorders; lymphocyte has a CPT code of 88233.
ICD9 (International classification of disease codes maintained by WHO (World Health Organization)) codes are used to describe the condition being treated. Examples
Stomach, unspecified Carcinoma ventriculi Gastric cancer ICD 9 code 151.9
Disseminated Carcinomatosis unspecified site (primary) (secondary) Generalized: cancer unspecified site (primary) (secondary) malignancy unspecified site (primary) (secondary) Multiple cancer unspecified site (primary) (secondary ICD 9 code 199.0
A Visit is a unique event having a definite time, place, provider and patient.
All visits capture standard information relating to this event only (Blood pressure, pulse, weight, height, diagnosis (ICD9 code(s) could be more than 1), procedures performed (CPT code(s) could be more than 1), medications given or prescribed (could be more than one, materials purchased, fluid intake, fluid discharge ect.). In a hospital a nurse may fill out a form containing this information every hour. (added during edit) In some cases some of this information may not be recorded for a visit. ( A once a year doctors visit would not record fluid intake
) -
Michelle/Dolphin,
Thanks for the feedback on the medical community... I guess that's one place where the following saying (author unknown) applies...
"Normalize 'till it hurts,
DeNormalize 'till it works."
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply