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

Get a really good book on data modeling - one written by a true database developer, not a .NET developer who tries to teach you how it's done in the "real world".

The problem space of database design is simply way too large to be significantly covered in a forum like this. Despite that though, I'll give you a few personal pointers:

Listen to the above posts about normalization. NEVER denormalize because you THINK that you have to for performance reasons. You should only denormalize after you've experience actual performance issues (ideally in your QA environment, not production). Even then, consider that there may be a better way to write your queries or improve indexing first.

Constrain the data as much as possible. Columns should be NOT NULL as much as possible. Use CHECK constraints and FOREIGN KEYs wherever they should be. If you don't do this, bad data will get into your database and cause a lot of headaches and special case programming.

Think through your data before you actually start designing tables. Get a good handle on how your processes will flow and what data they will need to track. Often times what you think is an entity at first glance turns out to be two entities. As an example, in a system that I'm working on, the previous designer created a Member table and all of the information from their application was part of the Member table. It turns out that a Member might want to change data that was on their application, but we still need to track what the original application looked like, so the Application is really its own entity and the Member is an entity that might initially be populated from the Application. In short, do extensive data analysis, don't just start creating tables.

Answer 2

Data Is Eternal. Processing Comes and Goes.

Get the relational model to be a high-fidelity representation of the real world. This matters more than anything else.

Processing will change and evolve for years. But your data -- and the data model -- can't evolve at the same pace and with the same flexibility. You can add processing, but you can't magically add information. You don't want to delete information (but you can ignore it.)

Get the model right. The entities and relationships in your diagrams should make rational sense to a casual non-technical user. Even the application programming should be simple, clear and precise.

If you're struggling with the model, don't invent big, complex queries or (worse) stored procedures to work around the problems. Procedural work-arounds are a costly mistake. Understand what you have, what you want to do, and apply the YAGNI principle to pare things down to the essentials.

Answer 3

I'd say an important thing to keep in mind is that the structure may change. So don't design yourself into a corner. Make sure whatever you do leaves you some "room" and even an avenue to migrate the data into a different structure some day.

Answer 4

Remember that normalisation is only relative to what you are modelling. Perhaps you are modelling a collection of objects in your domain. Maybe you are recording a series of events, in which data are repeated because the same data happen to apply at more than one time. Don't mix up the two things.

Answer 5

Understand the requirements as much as you possibly can up front. Then design a logical schema that will only have to change if the requirements change, or if you migrate to a completely different kind of database, like one that doesn't use SQL. Then refine and extend your design into a physical design that takes into account your particular DBMS product, your volume, your load, and your speed requirements.

Learn how to normalise, but also learn when to break the normalization rules.

Answer 6

I know this has been stated, but normalization, normalization, normalization is the key. If there is an instance where you feel that for whatever reason that you need to store data in a non-normalized format, don't do it. This should be handled through views or in a separate reporting database. My other key advice is to avoid text/ntext fields wherever possible.