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 1234»»»

Practical Methods: Naming Conventions Expand / Collapse
Author
Message
Posted Wednesday, February 28, 2007 7:33 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 431, Visits: 608
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mlato/2895.asp

Regards,
Michael Lato
Post #348115
Posted Tuesday, March 6, 2007 11:10 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 20, 2010 12:29 AM
Points: 123, Visits: 2

To the point.  A doc that DBA's and people in the design phase should commit to memory from day 1.  Note .... going down the wrong road and trying to get back to this point is not fun though!

Post #349593
Posted Wednesday, March 7, 2007 1:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 4:41 AM
Points: 288, Visits: 301

I don't agree with the naming conventions of identifying columns (<tablename>ID). That does not work with compound identifiers. First, you need more than the tablename, second, "ID" suggests that this column is an unique identifier.

I prefer using a PK (Primary Key) suffix or prefix.

Post #349610
Posted Wednesday, March 7, 2007 2:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 365, Visits: 959
  • tCustomer_CustomerID
  • tInvoice_CustomerID

Hmmm... so which one is the foreign key and which one is the primary key? Or more importantly which table is the foreign key referencing?

I tend to use pk_<tableName> and fk_<thisTableName>_<referrencingTableName>. If you wanted to know the columns being referrenced then the information is within the key.

What would happen with a composite unique key made up of three columns? I'd just do this:

uk_<tablename>

Post #349618
Posted Wednesday, March 7, 2007 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 7, 2007 1:13 AM
Points: 30, Visits: 1

I agree with most of what is presented in this article.  You can go over the top with naming conventions but this is about right IMHO. 

The compound key constraint naming is the exception to the rule as noted but that shouldn't stop you following this convention or a slight variation of this convention. 

Generally naming a fk constraint <table name>_<constrained col name> will work OK if your pk table uses <table name>ID as its primary key column - it's then obvious which is the pk and which the fk.  I prefer qualifying constraints with _FK/_PK/_DF suffixes rather than prefixes.  This means you can list sysobjects by name and objects belonging to the same parent are listed together.

I opt for no prefix for tables (and just avoid reserved key words) a vw- prefix for views and use v- to distinguish validation tables from data tables, also using a z- prefix for tables that contain system data rather than user data.  E.g. Customer, vSalutation, zVersion.

Post #349653
Posted Wednesday, March 7, 2007 5:01 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

I stopped reading as soon as I saw the recommendation to use Hungarian notation ("t" prefix for tables, etc.) I wish that foolishness had never been started!

 

Post #349658
Posted Wednesday, March 7, 2007 6:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 2, 2010 2:54 PM
Points: 56, Visits: 7

I advocate the use of English for naming standards. Never use abbreviations or acronyms. If the word can't be found on dictionary.com then don't use it. No prefixes or suffixes are necessary, just apply the rules of English that we all learn early in life.

A table that contains information about employees becomes the Employees table, a noun. A table is a collection similar entities, plural.

The stored procedure that inserts an employee to the Employees table is named AddEmployee.

Stored procedures perform actions considered verbs.

Wen the enterprise vocabulary becomes English the maintenance cost goes down. You write a program or system once and maintain it forever.

Post #349685
Posted Wednesday, March 7, 2007 7:41 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:47 AM
Points: 54, Visits: 428

While I agree with the essence of the previous posts, saying something is "nonsense" without constructively suggesting alternatives or even saying why is not helpful.

As for a statement like "never use abbreviations or acronyms", well ne dis jamais jamias (never say never).  As long as they are used sparingly and consistently I don't believe there will be problems.

I was dismayed to see the word "Data" included in one of the database name samples.  Terms like "Data" and "Information" should rarely if ever appear, as they add no descriptive value.  Was the db for an OLTP/TPS or a Mart? Well then say so in the name.

Your backup files suggestion is fair, and I have seen variations commonly used.  Although one could argue where environments keep the date and time as displayable/sortable file metadata, why bother with putting it in the file name?

In my experience, prefixing tables with a "t" (and views with anything) is unnecessary and counter productive.  For the most part, when doing development or maintenance, the person on the job always has the context of the object readily available, if not in the forefront of thoughts.  In any event SQL Server will easily tell you whether an object is a view or table or whatever.  Even when writing queries an objects "tableness" or "viewness" is not relevant.  Tables will have simple, short names when named correctly.  I believe they should be named in the plural (e.g., Users), although I keep entity-type names in data models singular.  View names tend to longer than table names because they should also describe the view's purpose with an allusion to whats in the where clause and/or whether its a UNION or OUTER JOIN and so on. 

In column names I think classwords add value.  I think "ID" should be used anywhere it makes sense (e.g., UserID), but always used as the classword for surrogate keys.  PhoneNumber is a much better name than Phone, as the Number classword means fixedlength string primarily comprised of digits. In my opinion, using prepositions like on and by should be avoided; classwords tend to make the column name clearer, as in CreateDate, CreateUserID.

I also think use of prefixes on columns indicating "indexness" or "keyness" is pointless and serves to obfuscate.

I could go on with more suggestions, but I've spent enough time on this already.  I hope my suggestions will give the author and other interested parties something to consider.

Regards all - Todd

Post #349724
Posted Wednesday, March 7, 2007 7:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 4:41 AM
Points: 288, Visits: 301

Lyle wrote: "A table that contains information about employees becomes the Employees table, a noun. A table is a collection similar entities, plural. "

I strongly vote for singular, because (OO) classes and tables are both but entities, and no java programmer would ever name a class plural. Accordingly a table should be named as an instance of the entity i. e. singular.

 

Post #349732
Posted Wednesday, March 7, 2007 8:06 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 5:36 AM
Points: 159, Visits: 20
the article intended to cover naming conventions but i found its names quite  uncommonly named. name employee table 'tEmployee'? give me a break.


steve yang
Post #349736
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse