vrijdag 25 maart 2011

SQL Server table best practices

Today the second post of the SQL Server best practices. In my former post i already mentioned the general best practices. In this post i'll explain my best practices regarding the table. Here are my best practices:
  • Use the prefix 'tbl'  for a table.
  • Namingconvention according to Camel Casing.
  • The names of the tables should be plural. Examples: Customers, Patients, ErrorMessages, etc.
  • If you build a OLTP system model your tables according to the 3NF. In some exceptions a denormalisation is possible because performance benefits.
  • Think about using schemas when you are using extensive prefixing for your tables.
  • Align your partition of your indexes with the partitioning of the tables. It is advisible to keep them in line.
  • Partition the tables when there are multiple millions of rows in it and there are multiple disks available.
  • When you're using lookup tables use a prefix or a suffix. example tblluTablename or tblTabelnameLU.  
  • The naming convention for assiociative tables (n:m relations) should be a concatenation of the derived tables. For instance for the tables tblProduct and tblSupplier the tablename is tblProducts_Suppliers.
Greetz,
Hennie

Geen opmerkingen:

Een reactie plaatsen