Data Modelling Standards

Table Names¶

• should be a SINGULAR noun
• should NOT have a "tab" prefix
• should NOT be a SQL reserved word
• should NOT contain the company name or application name (e.g., use "AppUser" instead of "AcmeUser")
• should NOT contain underscores
  

Column Names¶

• Primary Keys with an IDENTITY(1,1) (in SQL Server) or based on a SEQUENCE object (in Oracle) should be named the same as the table, but with an "ID" suffix. For example, the primary key of a "Customer" table would be "CustomerID". (Note that both the "D" is capitalized. It's "ID"; NOT "Id"!)
• Primary keys for configuration tables should NOT be an IDENTITY field
• Should NOT contain underscores
• The column containing the main descriptive text should be consistently called "Descrip" - OR - "FirstName" + "LastName".
• Where possible, columns with a foreign key should be named the same as the primary key field.
• Boolean (a.k.a. bit or yes/no) columns should be prefixed with "Is", "Has", or something similar
• Name should NOT contain the data type (e.g., no "Date" or "Time" in the column name)
• Auditing fields should be named "CreatedOn", "CreatedBy", "UpdatedOn", "UpdatedBy".
• Use the following standardized abbreviations
  • Num = Number
  • Descrip = Description
    

Views¶

• A view based primarily on a single table, with auxiliary information pulled in from other tables should be named with an "Ext" suffix. For example, if you have a "Customer" table, and create a view to pull in the name of the Customer's State/Province, etc., call this view "CustomerExt".