SQL Server: Example Naming Convention

An example of a SQL Server naming convention I have encountered:

Tables

  • tcTableName (e.g. tcAsset) for core tables, i.e. tables that contain regularly changing data and have insert/update/delete queries run against them.
  • trTableName (e.g. trAssetType) for reference tables, i.e. tables that contain static/unchanging data, usually used in reference to data in core tables.
  • tjTableName (e.g. tjUserRole) for join tables, i.e. tables that provide a many-to-many join between two other tables.

Columns

  • tableNameId (e.g. assetId) for primary keys.
  • foreignTableNameId (e.g. assetTypeId) for foreign keys.
  • name” for text fields in simple ID/Name tables, e.g. in trAssetType (assetTypeID int, name nvrachar(100)).
  • lowerUpperUpper (e.g. propertyEntranceStorey) for all other columns, with no particular convention on the column names other than the lowerUpperUpper case.

Views

  • vwTableNameSubset (e.g. vwAsset, vwWindowHistory, vwWindowHistoryLatest)

Other

  • Stored Procedures: uspTableAction (e.g. uspAssetDelete) (usp = User Stored Procedure – don’t use “sp” as its reserved for system stored procedures and SQL Server always checks the Master database for the SP first before checking the current database, meaning its bad for performance).
  • Functions: fnXXX
  • User-defined Data Types: uddtXXX
  • ID Columns: tableID / foreignTableID (e.g. assetID / assetTypeID)
Be Sociable, Share!
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *