Database Standards Part I: Defs, Abbreviation & Data Types

This is Part 1 or 6 blogs about database standards we use at my current employer. Though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is establish a set of standards and stick to them.

Definitions

We list some definitions are the beginning of our document. Included are Camel casing (vwVisitOrders) and Pascal casing (MedicalData)

Abbreviations

Though most developers like to spell everything out because they can use IntelliSense, abbreviations are ok for some common and industry standard abbreviations that can be used. Examples are Id (identification), SSN, DOB or Rpt (Report).

Data Types

Now, the standards for these are good because of the consistency factor, comparisons (WHERE clause) and SQL Server features. Some features of SQL Server get deprecated, and others are improvements. A good example is using varchar (max) instead of text data type.
   Yes/No: Use bit data type. We also suggest naming the field like a question: Isdeleted
   Flag/Status: use tinyint (1-Active, 0-Inactive, other numbers can be used for future status)
   Use Unicode only if necessary: nvarchar(100)
   Integers: Be sure to properly size up front, if not sure use int or bigint
   Fixed field character lengths: State - char(2)
   Variable character length: LastName varchar(25)
   Money/Decimal – use over float/real. Price decimal(10,2)
   Null/Not Null – won’t even discuss
   Max – use with varchar, nvarchar, vbinary instead of text and binary.

Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. It a deviation is needed, a group discussion should be voted on to change.

God Bless,
Thomas LeBlanc