December 5, 2012 at 9:28 am
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
December 5, 2012 at 10:00 am
no spaces
no reserved words
---------------------------------------------------------------------
December 5, 2012 at 10:16 am
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
December 6, 2012 at 5:38 pm
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
December 6, 2012 at 11:08 pm
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
Change is inevitable... Change for the better is not.
December 7, 2012 at 7:56 am
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.
December 7, 2012 at 9:20 am
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.
December 7, 2012 at 9:43 am
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!
December 7, 2012 at 10:07 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply