Editorials

What, More Database Naming Ideas?

by Ben Taylor

$$SWYNK$$

Featured Article(s)
Learning MySQL with PHP – Making the Connection to MySQL
This is a simple program to connect to mysql and extract information as easy as possible.how we can make a web page in PHP to connect to a MySQL database and show us the records on screen.

Featured White Paper(s)
Office 365 SharePoint Online – Architectural Considerations
Written by AvePoint

The goal of this white paper is to clarify strategies f… (read more)

What, More Database Naming Ideas?
Today we have a real goldmine of insight from our readers. I was going to move on to naming conventions for code; then I started getting a lot of Email today about database naming conventions. Reader responses have changed my plans, and I’m pleased to share them with you now.

Gord writes:

I have been working with databases for over 30 years now. Here are some of my observations on naming conventions:

  1. Table and view names should, as much as possible, be the singular version of a noun. Person, Customer, Inventory etc.
  2. Primary keys should be the name of the table plus a suffix. I use ID if system generated, No if application generated. PersonID, CustomerNo, InventoryNo etc.
  3. Foreign keys should have the same name as the primary key they reference. Sometimes, you need a prefix if you have multiple foreign keys to the same table. PrimaryContactNo, BillingContactNo.
  4. Avoid reserved words. Although you can have a column named Description, this is a reserved word so avoid it.
  5. Use the same name and the same definition for the same concept in all tables. If you use Note as a column in several tables, then use that name. Not Note in one table and Notes in another. Also, try to make them all the same definition. Not VARCHAR(1000) in one table and VARCHAR(1024) elsewhere.
  6. Use a consistent system of alias names. I keep a separate table of tables I use, with a brief description, and the alias. Usually, I use the Pascal HumpName convention for names, and make the alias each capitalized letter. This isn’t always possible, but it makes a great guide and makes cutting and pasting similar bits from one query to another much faster/easier.
  7. Stored Procedures are always in the NounVerb style (Not the VerbNoun style). This put CustomerSave and CustomerGet alphabetically close together. Much easier to find related bits.
  8. Variable names, wherever possible, should be identical to the columns they represent. If needed, use a prefix. (@CustomerNo or @MasterCustomerNo, not @CustomerMasterNo)
  9. If a variable is a parameter, I use a ‘p’ as a prefix. This makes it easier to distinguish parameters from derived or looked up values. (@pCustomerNo)
  10. Object names (Tables, Views, Columns, Stored procedures etc) use a mix of UpperLowerCase instead of UPPER_CASE_WITH_UNDERSCORE.
  11. When writing code, I put reserved words in all uppercase. This makes it much easier to quickly find relevant bits of logic and visually block out data objects (or the other way around).

David says:

  1. Good naming aims to support several objectives. To me, the priorities are (in order): a) prevent bugs, unexpected behavior and performance snags, b) encourage those making future changes to understand and embrace the defined architecture, c) help people new and old write queries without referring back to external documentation, d) facilitate future refactoring, e) help a new person understand the overall architecture and purpose of individual objects
  2. Consistency is important, especially within a single database. If abbreviations are used, the same abbreviations should be used consistently. (mingling CustID, CustomerName, CustmrContact becomes confusing to remember). Consistently use identical column names any place the same data is referenced (if OrderID is a primary key, referencing foreign keys should also be named OrderID, and not CustOrdID). Consistently use the same correlation aliases in queries.
  3. Extend consistency to definitions of objects: if you use “ID” as in CustomerID, VendorID, etc., make sure that these are always of the same data type, are always primary keys in the primary tables, that they are always indexed and have foreign key constraints defined in tables that reference the primary, and so on. In other words in this case “ID” should tell you not only about the meaning of the data in the column, but also the structure and behavior of the object.
  4. Always reference objects using 2-part names (schema.object), and use schemas where appropriate: besides helping manage security, grouping objects by schema can help organize things. (example: if you have a bunch of reporting-related stored procedures, consider naming them reports.MySampleReport etc.)
  5. Some things like prefixes with object type (tblXXX, vwYYY) or purpose (spgetCustomer, spdelVendor) are a matter of style and personal choice, with arguments for and against. Consistency is the important thing.

Maybe that can all be summarized by saying that good naming should first increase reliability and second increase convenience.

Brian shares some naming constraints when using Team Foundation Server version control for database objects:

Microsoft SQL developers that are considering using Visual Studio and Team Foundation Server to store their database object definitions in source control should be aware of the 260 character limit for file paths in TFS. See:

http://msdn.microsoft.com/en-us/library/ms245472.aspx

It is pretty easy to use up 80 characters or so just in general source control organization. Complicated TFS based branching models can increase that base amount. The Visual Studio SQL Database project can use another 80 characters prefix and 10 character postfix depending upon database name and schema names. So that leaves you with a max of database object length under 100 characters.

I consider it a good practice to limit your SQL Database Object names to 50 characters or less. Particularly stored procedures, constraints and keys since they are nested pretty deep in the project hierarchy.

Note: this is a TFS limit not a Visual Studio or SQL Server limit. Individual developers can work around this problem by mapping to shorter paths. The work around starts to break down when you have to perform TFS Merges across your branches. The work around essentially fails when you try to perform automated builds

My thanks to everyone who has made a contribution. I had a lot of other naming convention ideas come in today that are not directly related to databases; we can take look at some of those next week. You can send your thoughts to btaylor@sswug.org as you wish to share more insight or questions.

Cheers,

Ben

Tags:
Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.