Database Design: 6 Crucial Mistakes to Avoid

Which Mistakes should you avoid during Database Design? Today I’ll answer this question in detail. It’s a fact that creating a database is easy now, but when it comes to architecting it to perform optimally and give you the best return on investment, it is a bit difficult.

Many DBAs fail to achieve this goal of proper database design for optimum performance assurance. This article will discuss some of the common database design mistakes to avoid for proper database management.

Here are 6 Crucial Mistakes to avoid during Database Design

1. Ignoring the purpose of your database

While designing a database, you are doing it to meet some specific purposes of your business. Databases fundamentally store data from various sources and retrieve it based on the need of the situation. For this, you need to know how much data to be stored, what type of data to be stored, what the data is used for, and when and how to retrieve it.

If you are designing a database for data warehousing, then your requirement is different. A typical data warehouse is maybe having a greater number of data retrievals compared to writes. On the other hand, a transactional database will be having more inserts than retrieval. So, a database designer should understand the exact purpose of the enterprise database before designing its structure.

So, how can you know about the actual purpose of designing the most appropriate database structure? For this, you may ask the developers, business administrators, project managers, scrum masters, and even the end-users to gather collective inputs.

2. Poor normalization 

Normalization is converting rows and tables into a more meaningful structure, which will help avoid redundancy. Normalization follows a standard set of rules, which make sure the database structure is universally acceptable.

While designing an enterprise database, you must follow all concerning rules and procedures for normalization. However, you have to follow different sets of rules in terms of data warehousing. Normalizing the database is not something that has a standardized way to accomplish.

Different developers and different database administrators may be applying different sets of normalization rules and will use their own experience and understanding of the process to develop various database designs. For support regarding proper database design practices, you may get the assistance of expert remote database administration services like RemoteDBA.com.

But as long as you follow the standard rules of normalization, your database should have to be well designed. If it is not, you may end up with some trouble in database administration in the long run, like saving the same information multiple times, letting deletion of data cause some unwanted removals, or overwriting data.

Every database should adhere to the third normal form. If it is violated, then you should consider updating the database design.

3, Using Redundant data during database design

This issue is similar to normalization, as we discussed above, but with redundant data, we can still have the database seemingly normalized but have data redundancy. Redundant data is something that is unnecessary or the data which does not have to be stored.

For example. We can take the current age of a person as redundant data in a customer database. To keep the age up to date, we may require some calculation of given values. The best approach is to determine the age of a person by storing the date of birth and use a formula to calculate the age automatically by the system.

This way, the calculation is done elsewhere, and the current age is not stored in the database. In another example, if you maintain a central account management system with password validation, then you may not have to store the passwords elsewhere in your database.

In terms of normalization, this approach meets the third normal form’s gridlines, but there may still be the possibility of redundant data getting stored into the DB.

4. Using a single field containing multiple info

This is another possible mistake made while storing multiple pieces of information into a single field. We tend to do this usually at the initial phases of creating database tables.

However, as we are getting more diversified with various systems and the need for optimum performance, this becomes an issue. In the case of customer database systems, it is mostly seen in the address fields.

The address may consist of different pieces of information as street number, street name, city, state, country, ZIP code, etc. We tend to save all this information into a single field as an address while developing the CRM systems, which puts us into trouble later.

So, it is important to store data in separate fields, which makes it easier to retrieve information. If we want to find all the customers living in Texas or at a particular zip code, we can retrieve the exact data according to the field name.

5. Using business fields for the primary key

When you design the database tables, you need to consider custom defining the primary key for every table. It is a specific column or a set of columns that uniquely identify the given record.

In many articles and tutorials, you may see that one needs to find a business-relevant field or useful to the end uses as the primary key. For example, you can use primary key as social security number, individual taxation number, company ID, a part number for commerce products, etc. This is not a bad thing but may backfire.

It is recommendable that you create a new field to serve as your primary key. The purpose of a primary key is different than that of a business field. The objective of a primary key is to identify the given row uniquely. The business field may sometimes change in the future.

So, it depends on the rule of an external system, and if it changes, you may have issues with your data. So, it is very important to make your primary key unique and unchangeable. You may go through the naming conventions for primary keys to have a fair understanding of them and do it well while making database tables.

6. Using composite primary keys during database design

Another common mistake DBAs tend to make with primary keys is the usage of a composite primary key. This is not, in fact, a mistake as it is something very much possible in database design but not recommended. The composite primary key is something that contains two or more columns.

It usually happens when the given data rules determine a combination of two or more unique fields. You can find such combinations as customer first name, last name, date of birth, line number, order ID, and so on.

The actual issue here is also the same as using business fields for primary keys. So, the remedy is also the same as we discussed above, to create dedicated new fields for the primary key. In this case, too, the primary key field will serve a single purpose to identify the row within the database uniquely.

As a result of this, it is used as a foreign key in other tables. Usage of composite primary keys will need you to add additional two or three columns in the other tables to link back to the primary table, which is not an efficient way. If the business rules change over time and the composite key does not remain the same or if the format of it changes, then you will start to find problems with the data.

Along with these database design mistakes, you should also avoid poor referential integrity, poor indexing, inconsistent naming conventions, usage of spaces or quotes while naming tables, usage of reserved words for column and table names, and making the columns too small or large, etc.

What are your thoughts on these mistakes that could slow down or have a negative effect on your database design process? Leave a comment below.

Watch this space for updates in the Hacks category on Running Wolf’s Rant.