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, March 7, 2007 8: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 appreciate the fact that universities have been teaching the singular concept for years. It is a topic that I have debated with folks like Ted Codd and Chris Date for decades. I also love the controversy bred by this topic.

A roomful of human entities is not a gathering of person, but people. A company meeting is not attended by employee, but employees. Reinventing the language "wheel" is an unnecessary expense that leads to higher maintenance costs, longer development times, and overall confusion.

One of my first mentors was Peter Page', the inventor of NATURAL language. His intent was that the language of programming read like a great book. Have you read any great books lately?  

 

Post #349740
Posted Wednesday, March 7, 2007 8:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:50 AM
Points: 54, Visits: 423

I've gone back back and forth on the singular v. plural table names argument.  In the early days when I was a systems analyst, I was taught that table names should be singular because entity type names where named singularly.  Well I changed my mind in the last 2 or 3 years because a table is collection of instances, and it makes more sense to refer to a collection in the plural.

As for the "we don't do it that way with OO" argument, I must admit surprise that someone would try project an object oriented programming standard onto a relational database object.

For good discussion on general SQL naming conventions, including plurals see: http://www.dbazine.com/db2/db2-disarticles/gulutzan5

Regard all - Todd

Post #349743
Posted Wednesday, March 7, 2007 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404

What a can of worms.  For what it's worth here's another opinion.  Remember it's worth what you paid for it:

Prefixes - as a general rule I avoid them like the plague unless they server a very specific and limited purpose.

Tables - no to any prefix unless it's an enterprise wide database and then the prefix should be a "department" name or mnemoninc (hr, sls, mkt, eng, prd).  Prefixing a table with t is the same idea the author discourages when refering to data types in column names.  After all isn't a table the data type for a table?  Tables should be singular nouns.  If a table of Employee records is plural (Employees) then then the follow on logic is that the wrapper CRUD procedures would be CreateEmployees, ReadEmployees, UpdateEmployees and DeleteEmployees.  What if I only want one?  The name may make me wonder if it only creates multiples.

Views - one of the two places where I really like adding a type identifier (suffix) of _v or _vw.  Views are special case virtual tables and updateability rules will vary.  If I'm messing with a view in procedural code I want something that smacks me on the head to let me know I'm working with one. 

Procedures - See rule number.  In a moderately complex database you would end up with all Employee related procedures scattered all over the browser window.  Change to a suffix and magic happens because you then end up with Employee_get, Employee_ins, Employee_upd and Employee_upd.  Not really rocket science to find or figur out what those do.

Functions - the other place I like the type identifier and this time it's (gasp) a prefix (ufn_).  This is to let me know it's a user function.  Again this one smacks me on the head and lets me know I'm working with a user functoin and not a built in one.  ufn_Split(), ufn_LastDayOfMonth(), ufn_ZeroPad().

Relationships - I head to the OO world here with ChildTable_has_ParentTable.  It really helps out.

Indexs/Keys - Primary: PK_TableName or TableName_PK; Unique Key: UK or AK and TableName -- If I've got more than one I reevaluate my database design.  Foreign Key (index) FK and the ReferencedTableName.  Wheter you use a prefix or suffix is not relevant to me as I don't "code" against them -- just be consistent.

One place where the author and I do agree is on the need for being consistent.  I hate to alias anything that's used by another program (columns and parameters) so just don't go there.

And remember; opinions are like aXXholes, everyone has one and they're all full of it. 

 

 



--Paul Hunter
Post #349744
Posted Wednesday, March 7, 2007 9:07 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 5:56 PM
Points: 33,202, Visits: 15,348
I tend to favor using suffixes if you want to distinguish views from tables from procs, etc. The big reason is that I want all things affecting a particular area, i.e. Employees, to be grouped together when I'm looking at the objects. So table Employees is next to views, EmployeeSalary_v, EmployeesTerminated_v, etc.

Also uspEmployeeGetOne, uspEmployeeGetAll is easier for me to find in QA than uspGetEmployees, uspGetAllEmployees, which may be separated by many other objects in a list.

It always gets weird and hard when you start combining multiple objects into one function, but I think you need to do the best you can.

It's more important to be consistent than to worry about which standard. Within days, you'll pick up any standard and get used to it. But if things are haphazardly named, it's way harder than if every table has a t prefixed.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #349759
Posted Wednesday, March 7, 2007 11:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

I think Steve hit the nail on the head with why people get into using prefixes.  A lot of people got used to naming their database objects with prefixes precisely because they "group together" well in Enterprise Manager and other GUI tools, so they could easily group user-defined objects for easier management.

OTOH, with the addition of schema support in SQL 2005, I think the rationale/justification people use for built-in prefixes is gone.  Schema support in SQL 2005 and how it affects naming conventions would be a nice addition to this article.

Post #349785
Posted Wednesday, March 7, 2007 11:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
One of the things I have come across is that *not* prefixing or suffixing tables AND views may save the day on databases with large tables.

You maybe wondering "why?". Well you could change a table into a partitioned view ( with no client side changes ) or change a view into a table for materialization purposes ( again with no client-side changes)

These scenarios are not that uncommon and is something you may want to keep in mind.

Cheers,



* Noel
Post #349790
Posted Wednesday, March 7, 2007 11:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:00 AM
Points: 21,397, Visits: 9,612
Care to elaborate a bit more on this one?
Post #349793
Posted Thursday, March 8, 2007 12:52 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

Once again to the singular vs. plural topic: Of course there are valid arguments for using plural.

But most of them are weak especially the one that says: "An employee table contains employees so let us name it employees!". A table can contain less than 2 rows, do you want to rename it?

You can use whatever you want as long as you don't work for my company, but I do not see a reason for referring to a customer name as customers.name

And please don't counter like: "Customers is genitive without the apostrophe."

Post #349914
Posted Thursday, March 8, 2007 1:09 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:18 PM
Points: 442, Visits: 1,124

Just echoing other comments opposed to hard-coding an objects type in the object's name ("t", "tbl", "v", "viw", "int" - prefix or suffix): it is a blueprint for disaster.  As databases and applications grow and wane, tuning and other application factors change tables to views (partitioned views, tables moved to external databases and referenced through views, etc.), views become tables, and columns change datatypes (int -> bigint, char -> nvarchar).  Many database folks working with huge fluid systems shudder at Hungarian notation and all that follows.  It's only a matter of time before they have 50+ views that all begin with 'tbl'.

-Eddie



Eddie Wuerch
MCM: SQL
Post #349918
Posted Thursday, March 8, 2007 8:08 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

"You can use whatever you want as long as you don't work for my company, but I do not see a reason for referring to a customer name as customers.name "

Spoken like a true COBOL warrior. Who needs set processing?

Post #350049
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse