Tips for enterprise level database design

Question

I work with an enterprise application and have picked up some tips for DB design

1. All tables should have the following fields that helps in audit trail - LastChangedBy, LastChanged, LastChangedPage
2. All your stored procedures that have dynamic SQL should have the @bDebug parameter. By default it's set to 0. If it's set to 1, print out the dynamic SQL statement and this will be really helpful in debugging.
3. For CRUD SPs, have a way of partially updating the table. If your table has 10 fields and in one of the SP, you care about updating only 5 fields, have a layer of abstraction to do this.

Any other useful tips you can think of?

EDIT: Thanks for all the answers. I am still looking for an answer that can provide a link to tips/tricks/strategies for DB Design.

Answer 1

In my opinion one would require CreatedBy and Created fields.

Answer 2

For #1: Move to SQL Server 2008 and just turn on Change Data Capture. If you really need to keep detailed audit trails, this feature alone will justify the cost.

For #2: Any stored procedure with dynamic sql should automatically be put on double secret probation (ie: it's allowed, but has to go through multiple levels of code review to make sure there's not a better way to do it).

Answer 3

When it comes to the power of the web, its better never to delete anything. Therefore having a deletedOn date that you can just exclude those objects which have been "deleted" from your searches. This also helps frantic customers who accidently deleted their account. Obviously this shouldn't be used in every field.

Answer 4

A couple of thoughts that immediately spring to mind when working with very large database (VLDB):

Should you implement table partitioning?

Large database tables, with millions of records, may benefit from table partitioning.

• The availability of this SQL Server Feature is restricted to using the Enterprise Edition.
• The applicability is dependent on your platform hardware and the availability of an appropriate partitioning key within the table data.

What are my most frequently accessed tables?

Consider separation by Filegroup i.e. place Customer table on one Filegroup and Transaction table on another. This permits SQL Server to create multiple threads for file access creating the possibility of sequential I/O.

Then subsequently consider the underlying physical disk structure, i.e. a separate LUN for each Filegroup.

Devise a Flexible Indexing Strategy

You will no doubt already have an indexing strategy in mind however for VLDB platforms this should be frequently reviewed. As data volumes increase and data distribution changes so to may the execution plans for your data access queries. You should plan for the need to regularly review your indexing strategy.

Answer 5

LastChangedBy etc. fields are pretty useless. If you really need an audit trail, you need separate audit tables that detail the changes and maintain an audit history. If you don't need an audit trail, the LastChangedBy etc. fields are just added work for no business value.

Answer 6

Dates should be stored in Utc format and converted to local time at the client.