The proposed database architecture offers up to six columns that could be included in every table. It's not likely that all six are needed for every database, but some are fairly universal. It would be best to use the same set of standard columns in every table of a particular database. That's not a requirement, but consistency is desirable; consistency allows for powerful dynamic routines that work for any table in the database.
The six standard columns are:
| Name |
Data Type |
| CreateUser |
varchar(40) or int |
| CreateDate |
smalldatetime |
| ModifyUser |
varchar(40) or int |
| ModifyDate |
smalldatetime |
| DetectWork |
int |
| RecordMask |
tinyint |
The CreateUser column is used to identify the user that originally created the row. The column may contain a SQL Server login name, or it may contain a foreign key to a system user table if the application being supported has internal security. This column would be populated by the application.
The CreateDate column is used to store the date and time when the row was originally created. This column could be populated by the application being supported, by a default value or by a trigger. This architecture suggests using a trigger.
The ModifyUser column is used to identify the user that last modified the row. The column may contain a SQL Server login name or it may contain a foreign key to a system user table if the application being supported has internal security. This column would be populated by the application.
The ModifyDate column is used to store the date and time when the row was last modified. This column could be populated by the application being supported or by a trigger. This architecture suggests using a trigger.
The DetectWork column is used to detect concurrent access to a particular row.
Home: 
