Category: Database Design
Most Common Mistakes In A Database Design
Some of these problems are unavoidable and beyond your control. However, some of them are due to the quality of the database design.
Poor Pre-planning
If you are building a house, you would not hire a contractor and would immediately require them to start laying the foundation in an hour.
Bad design planning can lead to structural problems that would be costly to resolve once the database has been implemented.
Inadequate standardization
Database design is not a rigidly deterministic process. Two developers could follow the same design rules but still end up with completely different data designs.
That’s largely due to the inherent place of creativity in any software engineering project.
However, there are certain basic design principles that are vital to ensure that the database works optimally. One of these principles is standardization.
Standardization refers to the techniques used to disaggregate tables into constituent parts.
This is done until each table represents a single thing, while the columns describe the attributes of the element that the table represents.
Standardization is an old computing concept and has been around for more than three decades.
Bad indexing
Sometimes a user or an application may need to query numerous columns of a table.
As the number of records in the table increases, the time it takes for these queries will constantly increase.
To speed up queries and reduce the impact of overall table size, it is useful to index table columns so that the entries in each are available almost immediately when a SELECT query is invoked.
Unfortunately, accelerating the SELECT statement generally results in a slowdown of the INSERT, UPDATE, and DELETE statements.
A single table for all domain values
An all-encompassing domain table is not the best approach to database design.
Remember that relational databases are based on the idea that each object in the database is representative of one thing.
There should be no ambiguity about any dataset.
When navigating through the primary key, table name, column name, and relationships, one must quickly decipher what a data set means.
However, a persistent misconception about database design is that the more tables there are, the more confusing and complex the database will become.
This is often the reason for condensing multiple tables into one table, assuming it will simplify the layout.
This is true from an implementation point of view, but it is not the best way to design a database.
- Small domain tables will fit on a single page on your hard drive, unlike a large domain table that will likely span multiple sections of the disk. Having the tables on a single page means that data extraction can be accomplished with a single disk read.
- Having multiple domain tables does not prevent you from using an editor for all rows. Domain tables probably have the same underlying usage/structure.
