Database tables / fields you always use in your killer apps

Question

I'm hoping to tap into some collective experience here, so What (if any) utility tables, or common fields do you always include in your database design?

An example is that I always include an App_Errors table to store any uncaught exception information in, and an App_Audit table which stores all the edit information.

I've mooted (in my own mind) the benefit of including RecordCreatedDate and RecordLastEditedDate on each data table but not come to any conclusion as to whether or not the information really will be that useful.

To give the question a bit more direction - My current focus is globally accessible web application ( think social networking ).

Ta!

Answer 1

1 A table that contains a version number, so the app can easily check the version of the schema.

2 A table to hold arbitrary variable/value pairs, like a configuration file, but in the database. (You can put the version number in here....)

Answer 2

Every table, regardless of what it does, always starts with an "ID" field, int.

I also save an Errors table, which includes ID, datetime, failed method and overload, and a stack trace if available.

Sometimes I have a settings/stats table, but not always.

Answer 3

I have often used an audit log table to keep track of what data has been changed and by whom.

You'd be amazed at how regularly it has been of huge benefit.

Something else that comes up in almost every data model I work on is a variation on a status table, generally relating to the status lifecycle of the primary entity.

Answer 4

An error message table that has error code, technical message, user message, severity and memo columns. The severity and user message are used to build the message dialog when an error occurs. The error code and technical message are included in the error log (along with IP of client, date, time, etc) when logging the error.

Answer 5

For reporting, a numbers table (integers from 0 to 1Mil) and a table of static dates (30 years worth of days).

Answer 6

I tend to always have a table listing all the users that can access the system. I prefer this over creating a separate database user for everyone for connection pooling reasons.

Then there is generally a table listing the roles people can have in the system. A join table indicates who is allowed to do what. This setup is generally becomes more complicated with application-specific rules things in the business domain.

An audit table is a very good idea. I include a human-readable column so that non-technical people have a chance to figure out what's going on, as well as storing various keys and important values.

Answer 7

I find it's alway handy to include the following fields:

• Inactive (or Visible/Discontinued/etc)
• InactiveReason

Just makes it really easy to 'soft delete' items so data integrity is maintained and you don't lose that valuable historical info.

And +1 for the Audit Log table that Galwegian mentioned.

Answer 8

A membership table. Priceless.

Answer 9

Any table holding user data (as opposed to your system data) has a creation date and last updated date. If there is a 1% chance you might need to know when something happened. This will save a lot of headaches in the future. While I don't like triggers, using them to maintain these two fields is worth it. If you use real database logins, then created_user and updated_user are useful too.