Database Design Considerations

The bulk of this course has discussed technical rules for normalizing tables, using SQL to create tables.
In this module, we will take a step back and look at the big picture: what should you think about when creating a database? While the contents of this module are arranged around common design mistakes, you should remember the good habits you've learned throughout this course (and the previous one in the series) while keeping the mistakes mentioned in this module in the back of your mind.

Some of you who work with relational database management system (RDBMS) application programs may wonder why you should be concerned with database design. After all, most programs come with sample databases that you can copy and modify to suit your own needs, and you can even borrow tables from the sample databases and use them in other databases that you’ve created. Some programs also provide tools that will guide you through the process of defining and creating tables. However, these tools don’t actually help you design a database, they merely help you create the physical tables that you will include in the database.

Using modern database tools, just about anyone can build a database. The question is, will the resulting database be useful?
A database will not do you much good if you cannot get data out of it quickly, reliably, and consistently. It will not be useful if it is full of incorrect or contradictory data. It also will not be useful if it is stolen, lost, or corrupted by data that was only half written when the system crashed.
You can address all of these potential problems by using modern database tools, a good database design, and a pinch of common sense, but only if you understand what those problems are so you can avoid them. Step one in the quest for a useful database is understanding database goals.What should a database do? What makes a database useful and what problems can it solve? Working with a powerful database tool without goals is like flying a plane through clouds without a compass: you have the tools you need but no sense of direction. This module describes the goals of database design. By studying information containers such as files that can play the role of a database, it defines properties that good databases have and problems that they should avoid.

Forget for a moment that this course is about designing databases and consider software design in general. Software design plays a critical role in software development. The design lays out the general structure and direction that future development will take. It determines which parts of the system will interact with other parts. It decides which subsystems will provide support for other pieces of the application.
If an application's underlying design is flawed, the system as a whole is at risk. Bad assumptions in the design creep into the code at the application’s lowest levels, resulting in flawed subsystems. Higher-level systems built on those subsystems inherit the design flaws and soon their code is corrupted, too.
Sometimes a sort of decay pervades the entire system and nobody notices until relatively late in the project. The longer the project continues, the more entrenched the incorrect assumptions become and the more reluctant developers are to suggest scrapping the whole design and starting over. The longer problems remain in the system, the harder they are to remove. At some point, it may be easier to throw everything away and start over from scratch, a decision that few managers will want to present to upper management.