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.
(Assuming OLTP)
Normalisation of your data-structures. (Performance de-normalisations can generally follow later where needed)
Try to imagine the SQL queries that you will preform against it.
This is important because you will do it A LOT!
If you have queries that you're going to be running A LOT, make them into stored procedures. They will almost always run faster.
If you'll be looking rows up by fields other than the primary key, make sure to index them.
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.
Is it to an Object Oriented language? So try modelling your objects before the database. This will help you to focus on the model.
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.
Some things I would keep in mind. Make sure every table has a way to uniquely identify records (you will save untold hours of pain doing this). Normalize but do not join on large multi-column natural keys unless you want the whole thing to be slow. Use a numeric key that is autogenerated in the parent table instead.
Yes, think about the kinds of queries and reports you will need to run. Think about extensibility. It may seem like you wan't need more than 10 products columns in the order table but what happens when you need 11. Better to have an order table and an order detail table.
Make sure all data integrity rules are incorporated into the database. Not all data changes happen from the user interface and I've had to try to fix too many badly messed up databases because the designers figured it was OK to put all rules in the GUI.
The most critical things to consider when desiging are first how to ensure data integrity (if the data is meaningless then the database is useless) and second how to ensure performance. Do not use an object model to design a relational database unless you want bad performance.
The next most important thing is data protection and security. Users should never have direct access to the database tables. If your design requires dynamic SQL they will have to have that access. This is bad from the perspective of potential hacking in through things like SQL injection attacks, but even more importantly, it opens up your database for internal people commit fraud. Are there fields where you need to encrypt the data (credit card information, passwords, and Social Security numbers are among the items that should never be stored unencrypted). How do you plan to do that and how do you plan to audit decryption to ensure people are not decrypting when they have no need to see the data. Are there legal hoops you must go through (HIPPA and Sarbanes Oxley spring to mind)?
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.
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.
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.
I strongly echo that normalization is critical, with tactical de-normalization to follow for performance or other maintainability reasons. However, if you're expecting to have more than just a few tables, I'd like to offer one caveat about normalization that will make your life a lot easier as the number of tables grows.
The caveat is to make the primary key for each table a single numeric column (appropriate for your flavor of DB). In academic normalization, the idea is to combine whatever attributes (columns) of an entity (table) so that you can uniquely identify an instance of what is being described (row), and you can end up with a multi-column composite primary key. So then whenever you migrate that composite key as a foreign key to other tables, you end up duplicating those multiple columns in every table that references it. That might work for you if you only have half a dozen tables. But it falls apart quickly when you go much bigger than that.
So instead of a multi-column composite primary key, go with a sequential numeric primary key even though that approach goes against some of the strict normalization teachings.
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.
Since there have been several posts advocating this now, I'll add one more thing...
DON'T fall into the trap of putting ID columns on all of your tables. There are many VERY good reasons why modern database design theory uses real primary keys and they aren't strictly academic reasons. I've worked with databases that included hundreds of tables, many of which were multi-million row tables, with over 1000 concurrent users and using real primary keys did not "break down".
Using ID columns on all of your tables means that you will have to do multi-table joins to traverse across the database, which becomes a big hassle. It also tends to promote sloppy database design and even beyond that often results in problems with duplicate rows. Another issue is that when dealing with outside systems you now have to communicate these IDs around.
There are places for surrogate IDs - type code tables and conceptual tables (for example, a table of system rules could use an ID if the rules don't have real-world identifiers). Using them everywhere is a mistake IMO.
It's a long-standing debate, but that's my opinion on the matter, for what it's worth.
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.
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.
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.
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.
"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
Be practical. Keep in mind what your goals are and don't go crazy creating unnecessary complexity. I have some preferences:
But these are a means to an end (and are contradictory in many cases and require careful balancing), the main thing is to let the requirements drive the design. Your choice of what is a separate entity, and what is part of another entity, and what is cat food (not anything whose identity you care about) depends entirely on your requirements.
As much as you can make primary key a sequence generated number.