What is important to keep in mind when designing a

Question

What is important to keep in mind when designing a database?

I don't want to limit your answer to my needs as I am sure that others can benefit from your insights as well. But I am planning a content management system for a multi-client community driven site.

Answer 1

"Normalize till it hurts; de-normalize till it works."

Answer 2

Make sure that as much meta data as possible is encoded in the model. It should be possible to infer almost any business rule or concept from just looking at the data model.

This means, take care to pick names that reflect the reality of the users (but don't be afraid to change their perception of reality if it helps the model).

Encode all constraints you can in the database. Don't rely on the application layer to only supply sensible data. Make sure that only sensible data can exist in the first place.

Don't do aggregate data in the model. Keep the model as atomic as possible. Either aggregate on the fly or run regular aggregation jobs into aggregate tables.

Pick a good partition between schemas. Some partitioning makes sense to do with foreign keys, and some by pure physical seperation.

Answer 3

Don`t use a large set of columns as primary keys

Answer 4

(Assuming OLTP)

Normalisation of your data-structures. (Performance de-normalisations can generally follow later where needed)

http://en.wikipedia.org/wiki/Database_normalization

Answer 5

If you'll be looking rows up by fields other than the primary key, make sure to index them.

Answer 6

If you have queries that you're going to be running A LOT, make them into stored procedures. They will almost always run faster.