Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Hungarian notation convention Expand / Collapse
Author
Message
Posted Thursday, September 25, 2008 11:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:31 PM
Points: 441, Visits: 932
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.
Post #576239
Posted Thursday, September 25, 2008 4:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:15 PM
Points: 11, Visits: 35
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


  Post Attachments 
DatabaseNamingConventions_v1.1.rtf (495 views, 122.79 KB)
Post #576457
Posted Friday, September 26, 2008 5:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
Excellent Jane!
Can I steal it?
Post #576686
Posted Friday, September 26, 2008 10:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 6:59 AM
Points: 478, Visits: 1,411
These are really nice and thorough. I think I'll borrow them too :)
Post #576996
Posted Friday, September 26, 2008 11:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #577054
Posted Friday, September 26, 2008 11:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #577057
Posted Friday, September 26, 2008 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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... :P


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #577226
Posted Monday, September 29, 2008 4:18 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 1,493, Visits: 1,671
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/
Post #577607
Posted Monday, September 29, 2008 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357
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?
Post #577679
Posted Monday, September 29, 2008 7:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:31 PM
Points: 441, Visits: 932
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.


Post #577682
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse