What are the most important considerations when designing a database?

Question

I would like to know from the experienced programmers what they consider to be the most important considerations when designing a new database.

Answer 1

First off and most important learn and understand the business domain.

1) Are you looking at a high transaction rate like a busy web site, or low use like a a small company HR system

2) Is security a big issue - are you handling personal details, or financial data. Or is it just a product catalogue

3) Will your users be doing many updates/inserts or is it mainly read only

4) How many users, what are the usage patterns (peak load or evenly distributed)

5) Do you need 24x7, 16x5 or other uptime, 24x7 is much harder to do as you have no down time for maintenance

6) How big is the DB going to go? If it's really big you'll have to design your tables to take account of that and/or partition

7) Do you need to look at enterprise cluster with hot fail over, or just normal hosting

8) How will the DB be adminstered, in most DB projects 95% of the effort is spent developing for the users and their applications, DB admin is forgotten

9) DB Admin, from previous includes backups, changes to other systems, integration to other systems, data loading

10) Actually Data loading and using existing data is another big issue in its own right.

That's it for a start

Answer 2

The database is secondary to your business process design and should cleanly support your business process in a direct and simple way. You will gain far more benefit from a well-formed, clean, entity model than you will from an index here and there. So once your process is defined, you take it and split it up into "entities" as cleanly as possible with relations that make sense. Once you know your entities, they translate into database tables.

One of the most important things to do is to not overarchitect.

To give you an answer with some teeth, let's take a "vehicle" entity as an example. A vehicle has multiple wheels. You have a critical decision to make knowing that there will be multiple wheels attached to the vehicle. You have 2 choices to make - You can make "wheels" a separate entity, or you can make "number of wheels" an integer field in the "Vehicle" entity.

If you absolutely know that you will need to store lots of changing information about each wheel, then create a "Wheel" entity. You now have a relationship between entities (the car and the wheel).

If not, a simple field will do just fine.

Thinking through these critical decisions and making things as simple as possible is by far the most important thing for me when designing a database. It can make the difference between things being really easy and really difficult when you build the next layer(s) of your application.