SQL Server: Database Design Practices March 6th, 2013

Vinod Kumar

Talking with customers and solving their real world problems is something I love doing. This is my day job and I never get tired of this. As we meet customers almost every single day we also have our own feel good factors of suggestions. I am not calling these as best practices explicitly because these are general guidelines and there can be exception to these in your environment for the data set and project that you are working. So read this blog post keeping them in mind. Don’t use them something written on stone but as a mini-checklist used to meet your needs.

This will be Part I of many more parts to come in the future as I gather my thoughts on various suggestions I give customers. Do feel free to drop me a line if you want something covered here.

UPDATE: Part II: SQL Server: Security Practices and Checklist

* These are my perspectives of various DO’s and DONT’s. These can be different and you might have your very own standards. These are *general* tips and can be overridden based on the application under question.

Database Design Tips

This is a high level summary of a number of tips discussed later in this blog.

  1. Do create tables with a primary key.
  2. Do Not create tables without clustered index (typically we will have clustered primary keys).
  3. Do Not enforce foreign key relationships in the database with code. Always use Referential Integrity and foreign key declarations.
  4. Do Not develop a system by writing any code before the database schema is modeled and approved by design team.
  5. Do Not reverse-engineer the database schema after the system is built.
  6. Do have consistent data types between columns that are in a primary/foreign key relationship.
  7. Do make database objects descriptive.

Physical DB Model

A physical data model graphically represents the implementation of a database. It is constrained both by the logical data model it represents and the features of the underlying DB. The process of translating a logical model into a physical model includes the following tasks:

  1. Do create “legal” DB names for entities and attributes.
  2. Do assign appropriate data types and null-ability for each attribute.
  3. Do define primary keys, foreign keys and indexes.
  4. Do define rules and defaults.
  5. Do de-normalize if necessary for performance (typically in a Datamart or data Wharehouse database).

Design for Performance

As mentioned earlier, know what is the type of application we are designing. Is it an OLTP, OLAP or Reporting application. Based on this, designs can vary by miles.

  1. Do define the Primary Key on a single integer column which is typically implemented with an identity property. This keeps the dependent tables with a narrower row width and therefore, more rows per page which results in fewer scans.
  2. Do define the Primary Key as clustered. This keeps the secondary indexes much smaller.
  3. Do create non-clustered indexes on Foreign Key columns.
  4. Do make sure the tables have statistics. Keeping “Auto Update Statistics” and “Auto Create Statistics” options ON in the database is the default. For read only databases, it may be necessary to manually create statistics.
  5. Do select the correct “Recovery Model” for a database.
  6. Do use 100% Fill Factor on read-only or seldom updated tables.
  7. Do Not over-index, make sure you know which indexes will be used.

Naming Conventions

A database consists of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and triggers, defined to support activities performed with the data. The data stored in a database is usually related to a particular subject or process.

Database objects should be named clearly and unambiguously. The name should provide as much information as possible about the object. A name is used to uniquely identifies the object. Make sure this naming is consistent wherever the object is used throughout the database. Saving keystrokes should never be used as a criterion in selecting a name.

The following rules should be followed for all database object names:

  1. Do Not abbreviate unless absolutely necessary due to length restrictions.  If abbreviations are used, it should remain consistent throughout a project and be documented.
  2. Do Not use special characters when creating database objects.
  3. Do restrict names to alphanumeric characters.
  4. Do make the database objects descriptive.
  5. Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces (e.g., InvoiceDetail).
Abbreviation Standards
  1. Do avoid using abbreviations.
  2. Do Not use a different letter to start an abbreviation then the first letter of the word being abbreviated (i.e., XFER is not an acceptable abbreviation for Transfer).
  3. Do eliminate vowels first, followed by the least significant consonants of the word.
  4. Double letters can be eliminated unless they are required for clarity.
  5. Do Not use an abbreviation that strongly suggests another word. (Example: the word “parent” could be abbreviated PRNT but this would strongly suggest the word “print.”. A better abbreviation for parent is PARNT.)
  6. Do use consistent abbreviations of similar words. If Charge is CHRG then Change should be CHNG.
  7. Do Not use acronyms if the acronym is not generally known or is subject to multiple interpretations.
  8. Do use abbreviations consistently in the system.
  9. Do Not try to deviate from consistency. I have seen systems mark as _dt for some date field while the datatype is of Varchar. These are great confusions and a recipe for disaster.

I have seen a number of database designs and some of the abbreviations are pathetic and an eye sore. Sometimes when I read them and turn towards the developer – they carry a smile with a look that says: “I didn’t design or name the system :)”.

**** Below are a bunch of naming conventions I have seen. You are free to use them or even ignore them if they don’t fit the bill of your organization / project conventions. ****

Database Names

Database names should use the same convention as described above for database objects: no special characters, alphanumeric characters only and initial capitals for delimiters between words.

  1. Do Not use DB as a suffix for Database name.
  2. Do Not make the database name plural, Example: Use LegalPartner not LegalPartners.
  3. Do use qualifier suffixes for database names like ODS, DataMart, Factory, Staging, Extract and Archive for special type of databases.
  4. Do make the database name mnemonic and/or descriptive.
  5. Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces or underscores (e.g., LegalPartner).

Table Names

  1. Do Not make the table name plural, Example: Use Organization not Organizations.
  2. Do make the table name mnemonic and/or descriptive.
  3. Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces or underscores (e.g., BillingDetail).
  4. Do name Associative tables, tables created to resolve a one to many or many to many cardinality, a concatenation of the two referenced table names.
  5. Example: PersonAddress resolves many to many between the Person and Address tables.

Personally I am not a big fan of prefix of tbl to all the tables as it can be easily got from meta data of sys.tables in one shot easily.

The same principle applies to Views. But it is worthwhile to prefix the view based on where it is being used. This can be standard view (vw), Reporting View (rv), ETL view (etl) etc. So feel free to use them appropriately.

Column Names

This can get little complicated and specific to how people design systems. I like a three part name: [Primary][Qualifier][Classifier]. As the names suggests the Primary describes the column a bit more like in EnglishProductName or AlternateProductID. The qualifier can be the descriptive word like in: InventoryInHandQty. The final classifier can give me an idea of what datatype we might be using Qty = Quantity = Integer, ID = Identifier = BigInt, Dt = Data datatype etc.

  1. Do Not use spaces or special characters in column names.
  2. Do use underscores ONLY when making Foreign Key columns that require a Modifier to make the column name more understandable.
  3. Do Not abbreviate the column name.
  4. Do use the table name primary word when creating a primary key.
  5. Do use “ID” as a column name suffix, if it is an INTEGER identifier. If it’s a VARCHAR column it should end with “Code”.

The following attributes can be used where tracking when/who changed a row of data is required.

RowStatus_OptionID – (Optional) Used to mark row as Active/InActive.

Other General recommendation

  1. Do Not use spaces or special characters, underscores are the exception for stored procedures.
  2. Do Not name stored procedure with numbers (e.g. spc_myProc666).
  3. Do spell out the whole column name when defining Indexes.
  4. Do Not number the indexes.
  5. Do use the word “All” for an index that covers all columns in the table.
  6. Do use underscores between the column names help readability.
  7. Do name Primary Keys with PK_ followed by the table name, only.
  8. Do name Foreign Keys with FK_ followed by the child table name, then the column name.
  9. Do Not name Foreign Keys with number suffixes, as in FK_Table_Column01.
  10. Do Not use Double Underscores.
  11. Do name Check Constraints with CK_ followed by the table name, then a colum name.

Final Words

I know this is just a subset of what is available in your development checklist. These are just design list that I have made. The next will be add a security list, development list, deployment list, high-availability practices list etc. I think those will follow in the future.

Share this article

Tags: , , , , , , , , ,

This entry was posted on Wednesday, March 6th, 2013 at 08:00 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

13 Responses to “SQL Server: Database Design Practices”

  1. abhilash says:

    hi Vinod ,

    I don’t understand why you don’t like plurals in tablenames any reason do you want to share.

    • Vinod Kumar says:

      Well, it is more of a convenience that I would start with, but here are some reasons:
      1. Tables are just like containers, the table name must describe what it contains, no how much data it contains.
      2. Some of the ORM tools just auto-correct plurals to singular and that can be quite confusing.
      3. You can easily have irregularities when it comes to master-detail scenarios like: Orders and OrderDetails Tables.
      4. If I were to go with point 1 above, a single row contains details of a “single customer” so why should the table have plurals :).
      5. As developer it is far more easier to type and make lesser mistakes with plurals. Typical examples would be table names “Status” is less error prone when compared to “Statuses”. Yes, I agree auto-complete is always there. This is surely a thought too.
      6. The most silly reason would be to save extra keyboard hits :)

      As per ISO/IEC 1119-5, nouns are used in singular form and not in plural.

      Again, these are not written on stone but as guidelines. So feel free to disagree is what I feel.

  2. Sandip Pani says:

    Hi Vinod,
    Even though we follow database design practices , sometime we unknowingly miss some good design practices. All of the above tips are simple that is really important to make your database object name simple so that it can be understandable by all.

    This will definitely help as a check-list, during code review.

    Thank you for sharing this.


  3. Awesome Sir !! Really helpful a lot !!

  4. Nilesh says:

    Thanks for this wonderful post. :)

    Looking forward to upcoming posts.

  5. HARI JJ says:

    Hi Vinod,

    Thanks for sharing wonderful coding standards. Its very clear and informative. Expecting much more DB tips.

    Thanks again for the useful Post :)

  6. Samith C says:

    Simple but useful,

    Coding standards always helpful for time saving while going through an unknown procedures :)

    +1 For the useful Post :)

  7. Ram says:

    Valuable Information !!!

    Is there any tool which can analyse and validate this practices like we have FxCop in C# ?

    • Vinod Kumar says:

      Ram – There are tons of third party rules based tools available. Even Best Practices Analyzers from Microsoft help you do some bit of analysis. These are recommendations based on various experiences with customers that we give them. I am sure there can be something that can be build custom too.

      Having said that, thanks for dropping a line.

Leave a Reply