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

Is it to an Object Oriented language? So try modelling your objects before the database. This will help you to focus on the model.

Answer 2

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.

Answer 3

Be practical. Keep in mind what your goals are and don't go crazy creating unnecessary complexity. I have some preferences:

• Keep the number of tables small
• prefer narrow tables over wide ones full of null values.
• Normalization is generally good
• Triggers are typically very painful

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.

Answer 4

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)?

Answer 5

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.