Database design is a process that takes place early on the development life cycle of a new system. It’s an important part that shouldn’t be rushed because bad design practices can cost businesses more headaches and costs down the line, especially when changes are required to support business growth.
Good database design pays off. Here are some common database design problems.
1. Poor planning
Would you start building a house without a plan? The same goes for databases. It’s easy to get carried away when a project has received the green light from management. It’s best to take your time and get it right the first time to avoid costly disasters and unexpected downtimes due to a sluggish database.
Some of the questions that need to be answered: do developers need to be engaged for software development to build a solution that interfaces with the database? What will the scope of the project entail, how long is the entire project intended to run for?
An experienced database professional must be consulted to gather business requirements and to understand how the business works to establish how the database solution will meet those needs.
2. Communication issues between the business and tech
The communication between the business and the technical specialists must be sound to reduce the risk of requirements not being sufficiently understood. Both sides must not be afraid to ask questions and seek clarification. The purpose of the database is to store data so it can be accessed later.
A good database designer will know how to assess business requirements and understand what data needs to be represented, how it’s going to be accessed and at what rate, and what the operational volume will be. To do this, good communication must exist between the business and the designer.
3. A poorly documented database
Another common database design problem that ties into poor planning is that often this will also involve a lack of quality documentation through the entire development lifecycle. This includes comments within the database for example within stored procedures. If sound documentation isn’t produced this will become a problem when changes need to be made and developers do not have any reference material to draw from.
The planning phase should involve appropriate documentation and database diagrams such as an entity relationship diagram depicting the central tables and columns.
4. Ignoring Normalization
Normalization is a set of rules and refers to how data in a database is organized and stored. It is a process that should be planned before a database is built to ensure its longevity, so it doesn’t require too much maintenance or waste excessive disk space. Normalization supports the idea that every table should have a purpose and should cater to that purpose only.
Normalization, to the third normal form, if possible, keeps databases as efficient as they can be. Creating a normalized database eliminates duplicate or redundant data and inconsistent dependencies.
5. Redundant tables and fields
If a database isn’t planned properly it can lead to redundant tables and fields which can be a nightmare for developers to maintain, therefore normalization is so important. Database redundancy is where the same data is stored in two or more tables within a database. This means that if that data needs to be updated it needs to be updated multiple times and this can introduce errors and data inconsistency.
6. Poor naming standards
It may sound obvious, but names given to tables and fields are important. Having consistent naming conventions makes it easier to maintain a database or to expand the database and add more functionality. Often when database design and supporting documentation is rushed naming conventions go out the window. It may seem a small thing, but improper naming conventions increase risk and costs in database administration resources.
7. Bad referential integrity
One of the best tools that a database can provide is the enforcement of referential integrity. Every table should be given a primary key. When a primary key exists in another table, this is called a foreign key, and this dependency should be enforced within the database to ensure data integrity. It is essential to the health of the database that referential integrity is set up correctly early in the planning phase.
8. Insufficient indexing
Using database indexes increases database performance, particularly for larger databases that contain complex stored procedures (queries) for reading/writing/updating data. An index is a way of sorting data so it can be accessed quickly when a request is made. There is a downside however, they do take up storage space. In the planning phase, once tables and stored procedures have been defined, an experienced database professional can assess what indexing needs are required and strike the right balance to have the database running at an optimum level.
9. Not taking advantage of database engine features
A database engine is a powerful piece of software. A good database engineer will know how to harness this power to help guarantee that information in the database is correct and secure. Some of the database engine tools include stored procedures to access data, views to provide a quick and efficient way to look at data, functions to allow for complex calculations and triggers to automate changes when an event occurs.
10. Not considering the best way the database should be accessed and secured
Early on a database designer should consult with the business and other technical specialists on the best way for the database to be accessed. Does it need a front-end application to be built? This will also help to determine the best database software (for example SQL Server) for the overall IT solution. Database security should be considered in the design phase as well as user permissions and access levels.
Database design is an important step in the development lifecycle of any IT solution. Doing it right can save time and money. Talk to the experts at Everconnect to find out how they can support good database design.