Home
Schema - Do you have standard Tables and Columns?
  v3.0 Posted at 6/11/2019 10:01 AM by Tiago Araujo
​1. All tables should have the following fields:
FieldSQL Server Field Properties
CreatedUtcdatetime2 Allow Nulls=False Default=GETUTCDATE()
CreatedUserIdForeign Key to Users table, Allow Nulls=False
ModifiedUtcdatetime2 Allow Nulls=False Default=GETUTCDATE()
ModifiedUserIdForeign Key to Users table, Allow Nulls=False
Concurrencyrowversion Allow Nulls=Falsev

​The first three are examples of bad table records. The last one is an example of how this table structure should be entered.

imgGoodBadPracticesExampleSQLFields.png
Figure: 3 bad examples and 1 good example of Row auditing

Note #1: Never set the CreatedUtc field - instead use a default GETUTCDATE()
Note #2: These fields offer basic row auditing that will cover the majority of applications. When an application has specific auditing requirements, they should be analysed to see if this approach is sufficient.

2. All databases should have a table with one record to store application Defaults. This table should be called 'Control'.
If the settings are not application-wide, but just for that user then an XML (do not use an INI file) for simple stuff might be better. Examples are saving the 'User' for logon, 'Select Date Range' for a report, form positions, etc.

.NET programs have an Application.Configuration which exports to XML file (app.config) automatically. It works very well, and deployment is very simple. It's integrated right into the Visual Studio.NET designer as well.

3. All databases should have a version table to record structural changes to tables. See SSW Rules to Better Code​
 
4. Lookup tables that have just two columns should be consistent and follow this convention: CategoryId (int) and CategoryName (varchar(100)).

The benefit is that a generic lookup form can be used. You will just need the generic lookup form pass in the TableName and Column1 and Column2.

Note #3: The problem with the naming is the primary keys don't match
Note #4: The benefit with the character primary key columns is that queries and query strings have meaning Eg. http://www.ssw.com.au/ssw/Download/Download.aspx?GroupCategoryID=5BUS from this URL I can guess that it is in the business category.

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: