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

List of Database Naming Convention Decision Points Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:15 PM
Points: 30, Visits: 214
I am looking to find or create a robust list of decision points for database naming conventions. I'll start with a list and ask people to suggest useful additions. For this exercise I do not care to indicate an preference for any particular conventions, just to create a list that any organization could use to set their standards.

In no particular order, but numbered for possible reference:

1.Use of Hungarian (prefix or suffix) for object names ("tibbling")
2.Pluralizing of tables
3.Abbreviating and standard abbreviations ("abrvtng")
4.Use or avoidance of [Escaping]
5.Indication word boundaries (pascal, camel, _, etc.)
6.Capitalization scheme
7.Key naming
8.Association/join/many-to-many table names
Post #1393079
Posted Wednesday, December 5, 2012 10:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 5,872, Visits: 12,978
no spaces
no reserved words


---------------------------------------------------------------------

Post #1393104
Posted Wednesday, December 5, 2012 10:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
we use this one at our shop:
primary key and foreign key column name must contain name of the table...ie tbCity has tbCityID as it's PK, and all foreign key columns are name tbCityID
in the cases where multiple FK's need to exist, the FK must still START WITH the same name, so tbCityID_Business and tbCityID_Personal would be used , for example.

our "lookup" tables all start with tb or lu, more for sorting purposes than anything else. tables related to specific purposes/modules for an application are "tibbled", but with an eye towards organizing the tables by application or purpose. cmsEntities for contact managment, acctHeader/acctDetail for accounting tables, stuff like that.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1393114
Posted Thursday, December 6, 2012 5:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
use of schemas as containers for grouping and securing like objects

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1393807
Posted Thursday, December 6, 2012 11:08 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Karl Kieninger (12/5/2012)
1.Use of Hungarian (prefix or suffix) for object names ("tibbling")
2.Pluralizing of tables
3.Abbreviating and standard abbreviations ("abrvtng")
4.Use or avoidance of [Escaping]
5.Indication word boundaries (pascal, camel, _, etc.)
6.Capitalization scheme
7.Key naming
8.Association/join/many-to-many table names


I know at least one person who will take grand exception to my naming standards and use of identity columns. Others will take exception to my use of casing. That, notwithstanding, here's an overview of what I use based on your numbered questions. Even those good folks that take such exceptions have complimented the code I write as being extremely easy to read. I strongly enforce these conventions in code reviews at work. The exception to that is I don't enforce my flavor of casing on MS or 3rd party object/column names because things like Intellisense would really slow people down if they had to follow my casing conventions there.

Again, these are my particular conventions and I'm not even suggesting that they will meet anyone's approval. Also, when I'm doing work for some shop other than my own, I will follow their conventions even if I think they're ridiculous (like the one shop that had the convention that every comma must be on it's own line, everything had to be in lower case (with underscores, of course), and some insane abbreviation requirements).

1. I almost never use Hungarian notation. If I need to change a table to a view (for example) to keep underlying table change from necessitating a change in the GUI, I rename the table to something else and make a view with the original name. With Hungarian notation, the would mean I would have a view that had the prefix of "tbl". I've also had the need to change views to tables. The same goes for column names. If you change the data-type from int to bigint (for example), then you're either stuck with the wrong name everywhere or you need to change it everywhere. I will use a "cte" prefix for CTEs and I will prefix parameter variables with p, pi, po, or pio depending on what and how they are used.

2. I almost never pluralize table names. I'll have a table named "Company". I can't see having one called "Companies" especially if you use IDENTITY columns and follow the naming convention of tablenameID for such columns. To wit, I name the table after what a single row contains information about.

3. I almost never abbreviate because too many people have different standards and too many words end up having the same abbreviation. I'll use "N" in a Tally table but I never use "num", "numb", "nmbr", or "no" (for example. There are 128 characters available for most object names so I use intelligent naming and usually no abbreviations. With things like Intellisense and reasonable alias names, there's really no need for abbreviations anymore. Well, unless you're limited to 30 characters like in Oracle. An obvious exception is "ID".

4. I almost always try to avoid bracketed/quoted names. It's butt ugly and causes visual clutter, IMHO. There are exceptions especially when using non-plural names such as a table called "User". I think MS made some pretty big mistakes with their object naming especially when it comes to "Object_ID". You should never name a column the same as a function, IMHO.

5. For objects I create and call it what you will, I use mostly "InitialCaps" (first character of each word is capitalized)with no spaces or underscores for names ("ID" is always fully capitalized for me) and I only use underscores for system related prefixes (which is also another exception for abbreviations).

6. I use all upper case for keywords including functions and data-types. I use lower case for schema names and table aliases. For object names and variables, I'll use the "InitialCaps" I previously spoke of even if the object is MS or 3rd party provided for the sake of readability. Obviously, I need to preserve any underscores someone else uses but I really don't worry about case sensitive servers because I won't allow them in my shop. I occasionally have the need to have a case sensitive column but I'd likely quit a job that required me to make a case sensitive server. That's one of the big reasons I don't work with Oracle anymore. It defaults to case sensitive and I can't stand it. I do try to write case sensitive code on forums just to play nice for the people who may have case sensitive servers. I also find it odd that it would appear that MS has no such casing standard even on objects in the same database.

7. I use PK_tablename (tried to follow the MS convention to make life easier even though underscores are present). Same goes for IX and FK except that either column names are used or, for such objects with a lot of column names, I'll throw in the word "CompositeXX" ("XX" is a 2 digit number) to replace all of the column names. For FKs, I generally follow the MS standard and will repair any abbreviations/truncations.

8. As an exception to the "no underscores" rule that I usually use, I name bridge tables after the two tables the bridge and may or may not use an underscore to separate them depending on a choice by whomever I'm working for. I don't use both standards on any given system. I settle on one or the other and enforce it.

You should also have standards for indenting and commenting. My standard for commenting is that every Insert, Update, Delete, and Select (even correlated sub-queries and derived tables) must have a short comment explaining the "why" each should exist in the code. For my personal code, if you were to remove all of the code, you could draw a functional flowchart from the comments. Comments are the one thing I won't compromise on when working in another shop and I let them know that up front. They can remove them (there are tools that easily do such a thing) if they want but what I check into SVN (etc) will always have such comments. Most shops are highly appreciative of the comments, though.

I also strongly enforce the use of 2 part naming conventions and the general avoidance of 3 and 4 part naming except where absolutely necessary (in a Synonym, for example).

I have other conventions like avoiding exclusionary outer joins in favor of NOT IN or NOT EXISTING for performance reasons and avoiding the use of Table Variables to make troubleshooting easier but we'll leave most of those alone here.


--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 #1393864
Posted Friday, December 7, 2012 7:56 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 487, Visits: 1,365
One convention used by a previous employer that I found helpful and still use (with some flexibility) is to have a 3-letter prefix on all columns that is an abbreviation of the table name.
For example in the Employee table you have EmpFirstName, EmpLastName, etc.
For EmployeeAddress you use EmaStreet etc.
The primary key is always of the form EmpId and foreign keys would all be like EmaEmpId
This way there is no ambiguity on what tables they refer to and where the columns come from and no need to qualify the name if you have the same field in different tables.
3 letter prefixes get a bit limiting sometimes.
Post #1394071
Posted Friday, December 7, 2012 9:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:09 PM
Points: 3,108, Visits: 11,502
The most important thing for a naming convention is that it is always followed and enforced.

I have worked at several places with different naming conventions and they are all fairly easy to follow once you learn them.

When there is no naming convention or it is not enforced, then coding just slows down because you have to lookup object and column names all the time, especially when the same data item has different names in different tables, like OrderID, OrdID, Ord_ID, etc.

Post #1394119
Posted Friday, December 7, 2012 9:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:15 PM
Points: 30, Visits: 214
Thanks for the time take to respond. I am really not looking for particular conventions but rather to solidify a list of points that should be address as an organization sets it standard.

So instead of

Jeff Moden (12/6/2012)
I almost never pluralize table names. I'll have a table named "Company". I can't see having one called "Companies" especially if you use IDENTITY columns and follow the naming convention of tablenameID for such columns. To wit, I name the table after what a single row contains information about.


I would put on my list:

1. Are table names plural or singular? If plural, how are they pluralized? Are there common exceptions?
base ex: Company vs Companies vs Companys
example of possible exception: User vs Users vs AppUser [because user is a reserved word."

2. How are surrogate keys named?

example: id vs tableNameid vs idTableName vs tableName

Jeff Moden (12/6/2012)
I also strongly enforce the use of 2 part naming conventions and the general avoidance of 3 and 4 part naming except where absolutely necessary (in a Synonym, for example).


This statement of a standard implies a decision point along the lines of:

9. Where do we use 1,2,3 or 4 part names? Do we use 1 part names if the schema is dbo? Do we user 1 part names for references within a single schema? Where to we require 2,3 or 4 part names?

Good stuff all.

Thanks!
Post #1394142
Posted Friday, December 7, 2012 10:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 7,107, Visits: 12,661
It would be neat (at least to me) if after you pulled together your decision points and settled into a convention you posted those to this thread. I think it would be interesting to see the end-product. That is of course (minus any specific company or personal information) if you are at liberty to share it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1394152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse