Worst Practices - Part 1 of a Very Long Series!
-
Nice article.
Like a number of people who have replied, I do use Hungarian Notation, but only in for variable declarations in VB/SQL/ASP or whatever I am using.
To be honest, before this article, I never even considered it for Column Names or Table Names. To me, designing a DB in this way is asking for trouble..! Personally, I like to have Columns with a descriptive name to give me a clue as to the data, not the data type.
Clive Strong
-
Hi Folks,
Here are some column names that I have encountered in my database. I hope you are not using such column names. Maybe SQL Server might be lenient, but other databases are not.
1) AND/OR was implemented as [AND/OR]
2) COLUMN
3) SILLY*CO
4) START
5) LENGTH
This caused endless pains while migrating to another database. So please keep your database column names sensible.
Don’t use a feature just cos its there.
Cheers!
Abhijit
Edited by - abhi_develops on 12/09/2002 03:46:44 AM
-
As a coder and part-time DBA, I thoroughly agree with Hungarian notation in programs. Having worked on the maintenance phase of some large VB programs, it makes the maintenance and readability so much easier. However, relational databases are so much different. If we stick with the theory where each column is a domain, then naming the column in such a way that reflects the domain of values rather than prefixing the primitive datatype used in the representation seems to me, to be best way of thinking about it. I think that Interbase used to subscribe to this where you actually define your own domains (implemented as subtypes of the primitives available). When you changed the domain definition, it was immediately reflected throughout the database on all the fields declared as part of this domain. I can't remember if operations were allowed where you could add a number from the apple domain to the orange domain. I expect that you could if the underlying primitives were the same. My main point is, as coders, we should be shielded from the physical aspects of the database - we don't care. But we should be aware of the theory of the relational database. If the organisation has a number of domains that are of importance, then as a coder, you should be aware of the definition of these domains before touching the database. If a domain requires only positive integers, then immediately you have in-built validation. Surely this cannot be difficult to implement on SQL Server? Any thoughts on this? Finally, even if this never happens, fields are domain value pairs. Just because it is represented as an integer, varchar, etc., the principle remains the same. It is an abstract representation of a real world value and nothing to do with programming code.
-
All kidding aside, this might be a peeve. There are worse peeves. Like undocumented databases. I'd rahter have a tinyint column called iDoNothing with a corresponding document that states:
iDoNothing
-
Along the same lines of reasoning, has anyone written about the top WP on my list and my own personal major peeve: the unbelievable stupidity of prefixing table names with "tbl"?
As for column names I agree, but I think standards need to go further to the fundamental root: name columns what they naturally are.
A column named "purchase_price" is naturally going to be a decimal or money datatype, and who would think twice about what kind of data was in a column called "hit_count" or "due_date"?
SQL was originally intended to be english language-like to a point that a semi knowledgeable user could write queries. Therefor, does it not follow that the tables and columns be named in a way that a business user would most naturally refer to them? This is why the original convention of using underscores was used: to make it easier for the end user to read.
As others have noted, user defined datatypes are vastly under appreciated in the development community. I fear the MS enhancements to "alter table" will ultimately create more chaotic designs/implementations as it further promotes the "do before you consider" mentality of modern software development.
-
Nicely done, Andy - don't know where you get the time but it is appreciated!
Seeing you mention the 'replace var name in a s/proc', your 'what about the numerous s/procs, views, etc' & some of the other posts made me think about contracts & modularisation - the whole OO thing about encapsulating functionality & behaviour.
These objects always have an interface - a public contract between itself & whatever uses it. Internally, however, everything is isolated and who cares how it does it so long as it does it right... And everyone knows that a contract is (supposed to be) binding i.e. you don;t just go changing it willy-nilly.
Sooo, you publish something that something else can access and you give that a name (param name, method name, etc) then it CANNOT CHANGE...no arguments!
Along those lines it is then it doesn't matter whether you use Hungarian data type prefixes for e.g. s/proc params...the data type CANNOT CHANGE so ipso facto nor can/should the name. If it does then you broke the contract and you have a bigger problem on your hands.
The only question remaining is what you consider a published contract e.g. is a column name binding? Some would say yes, others no & some maybe's - I'm amongst the latter, since you could specify that NO access to the data store occur except via an s/proc - these in turn would access tables (& views at a push, being used IMO for nothing other than security but I prefer to avoid them altogether) so you could argue that to change a column name would only require changing s/procs content - not the published contract (i.e. s/proc names & params).
BUT - unless you have a nice search & replace tool, practically this is not often possible. And if you have the tool, does someone else also have it (if required)?
So I'm afraid that, although I like the idea of H/n naming in general (& use it wherever possible in e.g. VB) its not a practical idea.
Regards
Andy Davies -
After absolutely no conflicts with my employers and managers I have previoulsy managed to implement type based naming conventions, until my last employer. They couldn't predict the practibility or cost savings with such a project. I feel it probably has more to do with inexperience or laziness but either way I made no ground.
Apart from columns and variables, I also believe other db objects should also include a prefix, and if possible whether or not the object correlates to a defined database schema (data or dimension tables are a good example).
There is no reason not to have defined naming conventions within a database for dba's or developers. Considering that a database is a particular aspect of an IT dept it is also not unreasonable to expect it to have it's own specific objects and therefore naming convention.
To summarize, there are without a doubt more pros than cons with an established naming convention (not to mention programming and comment conventions). The confusion that usually follows with real text naming conventions often leads me to believe that naming conventions should be an industry level or departmental standard stamp of quality and cohesion.
Max
-
my personal conventions...
Tables:
- Users
- OrderDetail
Columns:
- FirstName
- UnitPrice
Sprocs:
- sp_
[action] e.g., sp_UsersInsert, sp_OrderDetailInsert
(Reason: New databases don't contain sp_* sprocs, so why bother with usp_*?)
T-SQL variables:
- have to admit it, sometimes I go hungarian for some reason, verging on VB-style, i.e., intCount or strFullName
C#:
- lean & mean: examples...
int count = 0;
string fullName = "Ima Hungarian";
Admittedly, my "conventions" lack convention.
-
I use the variable type prefix in stored procs, but you need to think about the column names to consistantly indicate their general datatype (number, character, decimal, etc..) to the developer.
ExpirationDate (datetime)
PaymentAmount (money or decimal)
PaymentNumber (integer)
OverDueInd (single Y/N char used as indicator)
OverDueFlag (bit or integer)
PaymentStatusId (integer)
that uses a lookup table to get the
PaymentStatusCode (short character code) and
PaymentStatusDesc (big character description of status code).
Best Practice would be to stick to a consistant small number of datatypes. Ive worked on systems with various numeric datatype lengths and had issue with getting a decent query plan out of them due to datatype conversion issues.
-
Speaking of asking the wrong question, I prefer to sidestep the question by striving to use code-generation technologies to generate most if not all of the coding interfaces to my database tables. This allows me to avoid the implicit challenge that comes from making my data model naming choices act as de facto programming interfaces.
In cases where I end up having to go directly from a database data model to business objects I emphatically do not use hungarian notation. The generated business object will be appropriately typed for the consuming environment, and with the help of the current generation of strongly typed languages and development environments, changes in data type are easily trapped and generally easily, or at least completely addressed.
In cases where I can use a tailored meta-data layer to generate both the database data model and the business object model I have even more flexibililty to manage the naming (again, no hungarian), and ensure that the data model typing issues are the best for the target database engine.
So, net-net, in cases where one has the luxury of generating the code that interacts with the database (as could be the case with just about every business application I've worked with), you can avoid the issues of overloading the role of data model names as both representations and interfaces altogether.
Dave H
-
As a long time VB programmer / SQL Server DBA, I would not use Hungarian for any object names is a database. One of the biggest challenges I have faced in the past few years is providing users with the ability to perform their own ad-hoc queries with tools such as MSQuery through Excel. If the objects in the database are all named using good old English, training and support costs decline dramatically. I typically tend to use column names that are self-defining.
So, a column that contains a payment amount from a client will most likely be named something like Client_Payment_Amount. Pretty easy to understand. I have much better things to do than answer phone calls or e-mails from a "suit" who's trying to write a simple ad-hoc query.
Plus, is a prefix of b or bi a bit, big int, or binary? How about sm, is it smallmoney, smalldatetime, or smallint?
In a database, my opinion is that Hungarian will cause more confusion that it would be attempting to solve.
My two cents.
Michale John
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
What a discussion we have going here! Well, here's my novel...
Hungarian notation?! Yuuuuuck!
I hate hungarian notation now that I have gotten used to not using it. The only place I use it is for database objects where naming collisions might take place. Things like indexes and foreign keys get prefixes that describe what they are, FK, UC, UX, IX, etc.
No hungarian notation on tables or columns. Not even on views, stored procedures or functions. This is not to say, however, that I don't use naming conventions. My tables follow standard object naming conventions (singular noun). Views are named similarly to tables but longer to describe their contents. Stored procedures don't get a useless usp_ in the front but are named based on the primary table that they hit and the action they are performing: Order_GetByCustomerID. I particularly like this one because they sort by table and clearly describe their purpose.
In .NET code the hungarian notation just gets in the way. Of course, this assumes you're using the VS .NET IDE. If you're using notepad and the compiler that ships with the framework then I can almost understand someone using it. Here's where the problem comes in there though. .NET is object oriented, that means that an object can actually be comprised of many different sub-types and interface implementations and the hungarian notation can cause confusion when iNumber is cast to IComparable and the ToString method is no longer available.
Hungarian notation implies magic. Objects should be named so as to describe their contents and not how they are used or what datatype they happen to be. If you need to provide that information, make sure you're names are descriptive.
Here are some examples of bad/good names:
Bad Good Why Amount Cost Count
Amount of what? Cost indicates that it is an amount of money (Decimal) while count indicates an Integer counter. Total TotalCost TotalCount
Received WasReceived WhenReceived
WasReceived would clearly be a boolean while WhenReceived indicates a date and time. See, you don't need hungarian notation unless the system you are using might result in naming collisions or something of the sort.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I agree with you that using Hungarian notation (or reverse Hungarian notation which I prefer) is a Worst Practice. But the cause of the worst practice is using the physical data type as part of the column name instead of a logical datatype. If you examine the MS page, there are only references to object names and not to physical data types (integers, strings) that you use in your example.
What are the opinions on using a logical data type implemented using the "user defined data type" capabilities of SQL Server? Since everyone may not be familiar with this capability, here is an example that solves the problem of the International Standard Book Number (ISBN) changing from 10 digits to 13 digits effective January 1st, 2007. One of the advantages of logical data types is that a physical change does not require any modifications to the PSM source.
-- create the existing schema:
sp_addtype @typename = 'Qty', @phystype = 'tinyint' , @nulltype = 'null', @owner = 'dbo'
go
sp_addtype @typename = 'ISBN', @phystype = 'char(10)' , @nulltype = 'null', @owner = 'dbo'
go
create table Books
( BookISBN ISBN not null
, OnHandQty qty not null constraint Books_D_OnHandQty default (0)
, OnOrderQty qty not null constraint Books_D_OnOrderQty default (0)
, constraint Books_P primary key clustered (BookISBN)
)
go
create procedure Books_add
( @BookISBN ISBN )
as
insert into Books (BookISBN) values (@BookISBN)
--
Here are the steps to change the physical implementation of ISBN:
1. Script out all persistent stored modules.
2. Drop all of the PSMs
3. Run this DDL to change all of the table columns:
sp_addtype @typename = 'ISBN_new', @phystype = 'char(13)' , @nulltype = 'null', @owner = 'dbo'
go
alter table books drop constraint Books_P
go
alter table books alter column BookISBN ISBN_new not null
go
alter table books add constraint Books_P primary key clustered (BookISBN)
go
4. Remove the old logical data type and then rename the new logical data type to the old name:
sp_droptype @typename = 'ISBN'
go
sp_rename @objname = 'ISBN_new' , @newname = 'ISBN', @objtype = 'USERDATATYPE'
go
-- rerun PSM creation scripts, note that there are no modifications to the source
create procedure Books_add
( @BookISBN ISBN )
as
insert into Books (BookISBN) values (@BookISBN)
go
-- Clean up
/*
drop procedure Books_add
go
drop table Books
go
sp_droptype @typename = 'Qty'
go
sp_droptype @typename = 'ISBN'
go
*/
SQL = Scarcely Qualifies as a Language
-
i'm a developer grown dba, but it has never occured to me that datatype of column names are more important than informationof hte table it belongs to.
i prefer to use column names like order(ord_id int, ord_date datetime...) order_detail(od_id int , od_ord_id int, ...) etc so that in SPs or triggers I always know which column belongs to which table especialy in joins. this most of the time avoids the requirement of table aliasing.
usualy if you are following standards, u can always remember what would be the datatype of your column. it require only common sence to understand that ord_date is either datetime and ord_desc is varchar. also, i always prefer the use of user defined datatypes.
so i feel it would be better to limit the use of Hungarian Notation with variables used in batches with limited scope.
--smk
-
i'm a developer grown dba, but it has never occured to me that datatype of column names are more important than informationof hte table it belongs to.
i prefer to use column names like order(ord_id int, ord_date datetime...) order_detail(od_id int , od_ord_id int, ...) etc so that in SPs or triggers I always know which column belongs to which table especialy in joins. this most of the time avoids the requirement of table aliasing.
usualy if you are following standards, u can always remember what would be the datatype of your column. it require only common sence to understand that ord_date is either datetime and ord_desc is varchar. also, i always prefer the use of user defined datatypes.
so i feel it would be better to limit the use of Hungarian Notation with variables used in batches with limited scope.
--smk
Viewing 15 posts - 31 through 45 (of 72 total)
You must be logged in to reply to this topic. Login to reply