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

Make sure you use constraints (CHECK, NOT NULL, FOREIGN KEY, PRIMARY KEY, and DEFAULT) to ensure that only correct data is stored in the database in the first place. You can always buy faster hardware but you cannot buy more correct data.

Answer 2

"Thumb rule of Databases - Down Always Beats Across!"

Examples: If you have a Customer table with columns for Mailing Address and Shipping address and Billing address... Create a separate CustomerAddress table with an Address Type

If you have a CancellationDetails table with CancellationReason01, CancellationReason02, CancellationReason03.. create a separate CancellationReason table

Answer 3

Establish consistent naming standards up-front. It will save several minutes of unnecessary thinking in the long run. (This may read as irony, but I am serious.)

And don't abbreviate anything, unless it is extremely common. Don't turn the database into a license-plate message guessing game. It's amazing what becomes not-obvious after a year.

Answer 4

As much as you can make primary key a sequence generated number.

Answer 5

I agree that knowing about your data is good and normalizing.

Something else I would suggest is to keep very large text fiels in a separate table. For example, if you have a contract you might want to keep a lot of the information about the contract in one table but keep the legal (and very large) document in a separate table. Just put in an index from the main table into the legal document.

Answer 6

Try to imagine the SQL queries that you will preform against it.

This is important because you will do it A LOT!