Setting up and administering an enterprise database is not an easy task. An administrator needs to be highly skilled and knowledgeable to ensure that the database is functioning perfectly, up and running as desired all the time. This article will discuss some of the major database design mistakes people tend to make and how to tackle those like a pro.
Devaluing the purpose of data
On designing databases, you need to ensure that it meets the business’s objectives and the application that uses it. Any data is stored and retrieved from a DB, and an ideal database will help you know:
- What the data it contains is used for?
- How frequently is the data inserted?
- How often, data get updated?
- How often the data get retrieved?
- How much data needs to be stored?
All of these aspects needed to be considered while designing databases. If you are designing a DB for data warehousing, then you may have to follow a different DB design than for regular transactions. Data warehouses may have to retrieve a higher volume of data but may have fewer inserts or updates. However, a transactional database may have a high number of inserts with limited reads.
Mistakes in Normalization
Normalization is an important step in database design, which helps transform a raw structure of tables into a proper database design, which may follow a defined set of rules to eliminate any redundancy. While designing an enterprise DB, you must follow the normalization rules and processes unless making a data warehouse. You may be following a different set of rules.
Normalizing a DB is not something everyone needs to do in the same standard way. Different database architects may be using a different set of normalization rules based on their experience and the enterprise objectives to meet. However, as long as you abide by the common rules of normalization, your design’s database must be designed well. Otherwise, you may end up with grave database issues like:
- Write the same data twice.
- Delete of data, which may use cause unintentional data removals.
If your database is not following the third normal form of normalization, you should rework it to update the design.
Data redundancy
Comparing with the errors related to normalization, data being redundant is also similar. However, you can still have a database that is well normalized but still have redundancy issues. Redundant data is unnecessary data or something which need not have to be stored. Say, for example, one person’s current age could be redundant data to be stored. This can be easily calculated lively based on the date of birth of that particular person so that the age can be updated automatically with easy calculation. So, it is always possible to avoid the database table for age by just storing the date of birth and the age to be calculated by the system. So, this calculation is done somewhere else and not stored in the DB.
Another classic example of redundancy is storing the user information which is already stored in other systems. In an instance where you use a centralized account management application with password validation, you need not have to store the passwords in the DB. It meets the third normal form guidelines but still means that there is redundant data. These types of data redundancy must be avoided. To know more about redundancy related troubles and ways to avoid it, you may consult with the experts of RemoteDBA.com.
Different pieces of information in a single field
Another major but common mistake by DBA is storing different pieces of info on a single field. This is often happening with the newbies who are new to database designing. However, as a DBA gets more and more experience and starts working on various systems by ensuring performance, storing data in this way may be a trouble. This is mostly seen in the address field.
They may tend to put one single field for address, consisting of various information like the house number, street name, city, country, and ZIP code, etc. So, it is strongly recommended that these fields be separated. Doing so will make it easier to retrieve information. Otherwise, it may be hard to identify the address, and it becomes difficult to update this data further in the future.
Usage of the primary key
While you design the tables, you should custom define one thing for each table as a primary key. A specific column or a set of columns has to be identified uniquely using these records. Many articles and tutorials suggest that you must try to find a useful field and business-relevant to be the primary key. Say, for example.
- You can make the Social Security Number (USA) or the Tax Filing Number as the primary key.
- You may consider a Company number like the ACN in Australia as the primary key.
- Consider the part number or an item number in eCommerce DBs as the primary key.
The purpose of a primary key may differ based on the business field it represents. It is important to make sure that the primary key server only identifies the row uniquely. Other fields that are used by the systems or users also have a unique purpose.
Compromised referential integrity
Referential integrity is all about ensuring the data getting on to a database is of high quality and can help meet the system’s objectives. Considering Oracle as an example, the major constraints for it in terms of implementation are:
- Foreign key constraint
- Not null constraint
- Unique constraint
- Check constraint, and
- Primary key constraint
If there are some specific rules for you to enforce on your data, then you can implement the same on your tables. If it is a rule on the data, then a field has to be entered, and it must go on to the table as a constraint. While following this practice, it is good to keep the validation as close as possible to the data. Any process for data addition or update may need to stick to the rules like the data loading processes and script by the developers. You can add the constraints to database tables with a single command.
This is not an exhaustive list, but avoiding these common mistakes can help you start your journey to be a DBA superhero.
