Some guidelines for the database design:
1. All tables must have the following fields to store the information of the record creator and the last update information.
- created_on datetime
- created_by nvarchar(255)
- modified_on datetime
- modified_by nvarchar(255)
2. All tables must have a primary key with 1 field only. This is to ensure that the record can be call out easily. (Note: this might violate the normalization design that you have learned from the text book!)
3. Use GUID type for the primary key type instead of integer type - this will ease the page navigation when you are putting the value in the navigation URL. Not easy to be hack by the "normal" user. This also reduce the chance for the user to create "GUID" value manually and insert the record to the table.
4. Reduce the use of self-referencing table design unless you have no other option - this is to simplify the SELECT statement that you are going to write especially when you are developing reports.
5. For generating report, the data should be stored in the predefined reporting table. This splits the development job from the application coding and move the data extraction process into SQL server by using stored procedure. It eases the testing on the data extraction and also speed up the application development (ie., parallelly doing the job).
