SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hungarian notation convention


Hungarian notation convention

Author
Message
J-440512
J-440512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 949
About using singular names for tables:

Good idea.

That name is then suitable for constructs such as

ForEach Officer ...

Looks pretty sorry when it is

ForEach Officers.
bossimal
bossimal
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 36
Hi All,

Hungarian notation is awful and inflexible. I have attached the database naming conventions I developed for our organisation (identifying references removed) and it's worked wonders for our databases and applications. Our previous databases were very messy and we often faced problems of changing data types etc as previously described by Jeff. Now, it's easier to read, code and maintain and the lagtime to get new developers up to speed has decreased as the notation is far more intuitive.

Any constructive feedback is welcome.

Jane
Attachments
DatabaseNamingConventions_v1.1.rtf (580 views, 122.00 KB)
Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2249 Visits: 1403
Excellent Jane!
Can I steal it?
Goldie Lesser
Goldie Lesser
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1686 Visits: 1501
These are really nice and thorough. I think I'll borrow them too Smile
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57509 Visits: 9730
Jeff Moden (9/23/2008)
Heh... include my post in your report on usage potential advising against using it.

The only time I even come close to Hungarian notation is that I'll use a "pi" or "po" prefix on parameters in long stored procedures just to make the parameters easier to identify on long procs.... but I darned sure won't prefix one with something like "i" or "int" because even variables and parameters can change requirements of time. Imagine having a bunch of GUI or T-SQL that passes named parameters to a proc and ultimately ending up thinking something was other than an Int because even though the data type for the variable changed, you couldn't change the name of the parameter for fear of breaking GUI code that did use named parameters.


I put "_in" on the end of input parameters, "_out" on the end of output parameters, and "_inout" on bidirectional parameters. That's at the end of the name. Makes it very easy to tell, hundreds of lines into a complex proc, which things are parameters and which are locally declared variables.

The data type is too easy to end up having to change, like you say, so I don't include that. Just the direction. I've found it very useful.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57509 Visits: 9730
J (9/25/2008)
About using singular names for tables:

Good idea.

That name is then suitable for constructs such as

ForEach Officer ...

Looks pretty sorry when it is

ForEach Officers.


In that one case, yes, the singular name looks like it makes more sense. But I don't find:

select Name
from Officer -- Is there only one officer?

to make sense. Update, Insert, Delete and Select look more natural to me when the table name is plural, and I use those a LOT more often that I'd ever use a ForEach contstruct.

That's just an opinion, but it does feel more natural to me that way.

I use singular column names, because each column should contain 1 and only 1 value for each row. Again, totally arbitrary, but it just feels right to me that way. Plural for tables, to indicate the multiplicity of rows, singular for columns to indicate the normalization of the table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214218 Visits: 41979
The basic table naming convention I try to follow is... what does one row contain? Does it contain a Customer or Customers (answer should be obvious, there)? Same for User, Officer, etc, etc. I also generally try to stay away from "User" because it's a reserver word.

For lookup, definition, or reference tables (whatever you prefer to call them), I follow the ol' "parent table" naming convention. For example... If I have a table called "Customer" and I want to include a custom "type" (ie, business, residential, internal, etc) column in the Customer table (would normally be a CustomerTypeID column), the name of the table to find the ID/Name cross reference would simply be CustomerType and it's primary key column would, in fact, be CustomerTypeID.

For disassociated (ie. General purpose) lookup tables, the name would obviously be a table name with no embedded parent. Further, in order to differentiate between internal "ID's" and "Code's", such as the 2 letter abbreviation for a State or Province, I may have a table called "State" which may or may not have StateID, StateCode, and LongName columns.

Ok, duck... here come the natural key zealots... Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NicHopper
NicHopper
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2912 Visits: 1944
Hi all,

Jane, thanks for posting your article, one things I did notice was;

"Stored procedures should be named by function and the object they work on. Standard prefix like ‘p’, ‘usp’ or ‘sp_’ (contra-indicated by Microsoft for performance reasons) are not recommended"

I understand about 'sp_' as this indicates a system stored procedure to SQL, however does the 'usp' also indicate this?

Thanks,

Jackal

------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Andrew Gothard-467944
Andrew Gothard-467944
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 2357
michael vessey (9/24/2008)
Agree Completely..

not just tables and views though - I've inherited a Database written by a .Net developer and all my columns have prefixes and suffixes

for example

Mytable_Mycolumns_tinyint


Particularly odd given that standard procedure for .NET is to do away with Hungarian anyway.
And have I missed something, but do you mean you're getting things like Customer_Postcode_Char as a column in the customer table?
J-440512
J-440512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 949
About naming parameters:

I have been using @pi_ prefix for "parameter, integer" and @ls_ prefix for "local variable, string".

I am one who uses very long stored proedures to say, process incoming Electronic Data Interchange incoming customer purchase orders with multiple validaton steps, etc. And I am not talking about a few hundred lines either. TGhink about an order of magnitude higher. All set-based but (quick, hide the children) procedural code.

Never swa the need to distihguish between input and output parameters, in fact, I have yet to use an output parameter. I welcome any feedback as how an output parameter could be useful in a practical applicaiton.

One thing I will do is return a result code to identify processing errors, and for this I use a local variable instead of an OUTPUT parameter.:

DECLARE @li_RetCode int
SET @li_RetCode = -1


IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @l;i_RetCode = 0

IF @li_RetCode <> 0 RETURN @li_RetCode


IF @@ERROR <> 0 SET @li_RetCode = -2



OR



IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0

IF @li_RetCode = 0 BEGIN
.
. IF @@ERROR <> 0 SET @li_RetCode = -2
END




OR

DECLARE @lb_InTransaction bit
SET @lb_InTransaction = 0

IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0
IF @li_RetCode <> 0 GOTO RATS


BEGIN TRAN
SET @lb_InTransaction = 0


IF @@ERROR <> 0 SET @li_RetCode = -2
IF @li_RetCode <> 0 GOTO RATS


IF @@ERROR <> 0 SET @li_RetCode = -2
IF @li_RetCode <> 0 GOTO RATS

COMMIT TRAN
RETURN @li_RetCode

RATS:
IF @lb_InTransaction = 1 ROLLBACK TRAN
RETURN @li_RetCode


Any constructive comment on the above is welcome, especially if someone can point out why this is a bad idea.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search