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


List of Database Naming Convention Decision Points


List of Database Naming Convention Decision Points

Author
Message
Karl Kieninger
Karl Kieninger
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 289
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
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25182 Visits: 13701
no spaces
no reserved words

---------------------------------------------------------------------
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73665 Visits: 40974
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40676 Visits: 14413
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219150 Visits: 42002
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. :-D 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.
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
timwell
timwell
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: 1711 Visits: 3217
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.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14831 Visits: 11848
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.
Karl Kieninger
Karl Kieninger
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 289
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!
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40676 Visits: 14413
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
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