What naming convention should I use in my database?
Unless you work with Celko, there really isn't a right or wrong way to name your database objects. Some ways are better than others, of course. There are three important questions to consider:
Rather than try and push my own conventions on you, I'm just going to go through the more common object types, tell you what I use (and why), and where necessary, tell you what I stay away from (and why). I'm anticipating a lot of flack here, so please remember, these are *my* conventions, and the justification of my conventions are merely *my* opinions. So please, no flames, no comments that my conventions are stupid, and no "my convention can beat up your convention" crap. If you feel any of these urges, please start over from the top of the page. I think I've made it very clear that this is not a "you need to do this" article, but rather a "this is what I do; learn from it if you like" article.
There are a few simple rules I live by when it comes to the name of any object in a database (including the name of the database itself):
Tables / Views
Tables are sets of entities in the data model; as such, their names should be plural/collective nouns. The same goes for views: from a relational standpoint, they are just like a table, and represent sets of rows. The example I see Celko always talk about is a table representing employees. "Employee is bad, Employees is better, Personnel is best." Some people have this habit of singular table names, and their justification is either that you are describing a single row, or that it is possible that the table will have exactly one row (making the plural kind of silly). My answer to the first point has already been stated: you are describing a collection of rows, not a single row within that collection. And to the second point: there aren't many tables out there that have exactly one row, at least not for any tangible amount of time, and not likely on purpose. One example I can think of is a poorly-designed properties table for an app, e.g.
However, even though there is only one row, you wouldn't call this Property, would you? So, maybe singular makes sense for a table with exactly one row and exactly one column. But then, why the heck is that a table, anyway?
Like the Hungarian notation points above, there is an alarmingly common tendency to name tables with a "tbl" prefix. I don't find any valid reason for this convention, and while I could just say, "such a prefix is stupid," I'm sure people out there have their reasons. One might be to distinguish tables from views, however I don't see where this would really be necessary. The only thing I tend to distinguish between tables and views is using a v prefix on the latter, and even then, only when the view is not updateable. This just alerts me that the view is designed for SELECT only, and is much better than a "_notUpdateable" suffix.
Now, I don't have a problem using *functional* and meaningful prefixes on table names. For example, Sales_Personnel and Support_Personnel. If these entities are really separate in your data model, then there is nothing wrong with naming them appropriately, so that access to them is unambiguous. A nice benefit, also, is that they will group together nicely in any GUI that presents the list of tables in alphabetical order.
Column names represent an attribute of the entity in your data model; so, they should be singular, and should reflect the entity, not the table name. There is no reason for this:
When this would suffice:
When referencing these columns, there is no reason to repeat the fact that they involve an employee. Even when joining against another table, you can pull these out as table_name.column_name or alias.column_name, so it is never ambiguous.
However, there is one exception to my rule that you may have noticed: any column that is going to be referenced from other tables should contain the basic concept of what it is, so EmployeeID is in the Employees table as a primary key, and is also in the Payroll table as a foreign key. I see columns named "ID" all the time, and my knee-jerk reaction is to just rename it, and comment on why I did so using sp_addextendedproperty. :-)
Stored Procedures and Functions
My tendency with stored procedure names is to explain what they do in plain English, using a verb-noun scheme. Prefixes that tell you right off the bat what the procedure is going to do for you, like GetCustomerDetails, AddComment, EditArticle, RemovePermissions, ArchiveFileDetails. Remember, programmers and other non-DBAs will be using these procedures--they aren't likely to care about the actual database implementation (most end users don't need to know the difference between DELETE, TRUNCATE, DROP/CREATE, they just need to know that when they call EmptyLog, the Log table will thereafter contain 0 rows). My scalar and table-valued UDFs almost unilaterally start with the word "Get," because they are always used to retrieve something.
Do not use the "sp_" prefix, for a couple of reasons. One is that sp_ procedures have a special meaning to SQL Server, and when calling it, the server will check the master database first to see if there is a system procedure with that name (and this can be a minor performance hit). Another is that sp_ is kind of a "reserved prefix"... Microsoft will continue to name their procedures this way, so even if you find the performance hit negligible and decide to use the prefix anyway, there is a chance that a service pack, hotfix or new version of SQL Server will introduce a new stored procedure with the same name as yours, and now the engine will call the one in master and your application will break.
Another prefix to avoid is the "dt_", as this is used for procedures that are created with tools like Database Diagrams and Visual Studio. Having these prefixes won't cause any performance issues, as they are merely local to the database, however it is feasible that before the dt_ procedures are created, you will use a common name (causing a conflict). For more information about the dt_ procedures, see Article #2534.
Similarly, do not use the "fn_" prefix for functions. While I am not aware of performance implications, there is certainly the possibility of your own functions getting lost within those that ship with SQL Server.
The dbo. prefix is important when creating functions, because there are many cases where you simply can't call a function unless you prefix it with the owner name (this is not true with any other object).
Like tables, functional prefixes like Reporting_ and Admin_ can be used to group functionally-related procedures and functions. This can help both in organizing your objects from a management perspective, and recognizing functional areas within T-SQL code where these objects are referenced.
I usually put the name of the table in the trigger, prefixed by the action I want the trigger to perform. For example, PreventDuplicateEmployees would be a trigger that prevents the same employee from being inserted into the table multiple times (if, for some reason, constraints didn't or couldn't prevent that in the first place). PreventDuplicates wouldn't be very wise, because trigger names must be unique within the database, not the table—so I would have to adjust the name of the trigger if I wanted the same kind of activity on any other table.
I don't find prefixes like tr_ or trig_ very useful here. I know I'm dealing with a trigger because I had to go through the Triggers node. I don't believe I have ever had to trace a reference to a trigger in T-SQL code, and try to figure out what kind of object it is, because triggers are not typically found in any code at all.
It can be useful to include the triggering action of the trigger in the name, such as PreventDuplicateEmployees_Insert, but I don't find this essential. Even if the name doesn't make it clear what kind of action will spring it to life, usually if I am interested in the trigger, I am going to view the source code anyway, and right there is the FOR [INSERT][UPDATE][DELETE] statement.
Like triggers, since indexes are not typically referenced explicitly in code (and such references do not need to be reverse engineered), there is little value in thinking too hard about the name applied. I tend to indicate whether the index is clustered, non-clustered, unique, non-unique, and which column(s) it applies to. So I might have a non-clustered index on Employees.LastName, Employees.FirstName, and it might be called NC_Employees_Name. Like several of the other object types, there is little use in prefixing with i or idx, because there just aren't enough scenarios where an index can be confused with any other kind of object. Unless, for whatever reason, you're trying to model a system by looking only at:
Keys, Defaults and other Constraints
I don't have any hard and fast rules here. Usually, I don't have a need to know the names that the system sees attached to my keys and constraints, unless I need to disable or drop them programmatically. Which is usually an administrative task, not one which is performed from T-SQL code. Even then, I'm going to retrieve the name(s) from the metadata anyway, because it is unlikely that any convention can be assumed to have been followed. If I have violated a constraint and need to drop it temporarily, chances are, I already know the name because it is in the error message.
So, I let the system name these things on its own. Instead of creating a table and *then* applying keys, constraints and defaults, I build the CREATE TABLE statement with the definitions for those elements included.
If I were to recommend how to name these things, I suppose I would differentiate primary keys and foreign keys the same way the system does, however I would not include column names. This can get very cumbersome if you have a composite key, and even more so if you have several such references in one table. So maybe I would have PK_Employees as the primary key on the Employees table, and for EmployeeID in payroll, the foreign key would be named FK_Payroll_Employees.
Defaults and constraints could have similar logic applied, but I do not advocate naming them with the values that are used. If I set a default value for a SMALLDATETIME column called TraceStart, I might use DF_TraceStarted. If I have a constraint on a column named salary, maybe it is C_Salary. Bad names in these cases would be DF_TraceStarted_GetDate and C_Salary_Between_0_And_500000; mainly because I can look at the object to determine what it is doing, and if I change the constraint I also have to change its name. Whereas the PK and FK are much less likely to undergo a change of that scope.
Well, that's about it. I was going to get into other things like users, logins, user-defined data types, DTS packages, but then it started getting late. If you stick to my general suggestions above, most of these other things will just shape themselves.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?