When you're designing tables for a new database or schema, there are a few things that EVERY table should have. A little forethought can save you a ton of time, and a lot of data cleanup/scrubbing down the road. Anyway figured I'd run down a quick list of things I always do....and that the inherited DB I'm currently working on sorely lacks.

1. Every table should have CreatedDate and ModifiedDate columns. These columns should both be set to a default value of the date and time the row was created. In SQL Server this can easily be done by setting the default to GETDATE(). In addition the ModifiedDate should be updated by any procedure or query that updates any value for the row. This information is invaluable if you need to track down when things went wrong in your DB

2. Every table should have CreatedBy and ModifiedBy columns. Even if you're the only user in the system, add the columns to your tables. It's surprising how often a system built for 1 turns into a system used by many.

3. Define a primary key/unique constraint on every table. Just the exercise alone of doing this can help you understand your data better, not to mention it prevents duplicates.....a very common problem.

4. Build relationship constraints between your tables. This helps prevent orphaned data in your database...again a very common problem. It also makes your database much easier for someone other than you to understand. The very exercise of defining the relationships will also solidify your knowledge of the data you're dealing with, and maybe even uncover some oversights.

5. Keep Null values out of your database when possible. If it's required information, let the database know that by having the column not allow NULLs, or by assigning a default value if appropriate.

I wanted to keep this list pretty short, but these 5 things will go a LONG way to improving data integrity in your database. They will also help anyone tasked with implementing enhancements or fixing bugs in the future.