10 signs your database isn’t good enough
When business systems are running slowly or inconsistently, many are often quick to blame the database – and they’re usually right! Database issues can result in many of the most frustrating challenges we face when using software, such as slow progress, inability to save, or data return errors.
Find your database weaknesses
Identifying the weak spots in your business databases is not always a straightforward task, depending on the complexity of your data structure. Still, taking the time needed to address the root cause of any problems can make a huge difference.
Let’s take a look at 10 of the strongest signs your database may need some attention:
1. Poor planning
Often, the database is a critical part of the business that is needed urgently at the start of a project. However, it’s important to resist the temptation to build it quickly just to get up and running. Without proper planning and design, you run the risk of outgrowing the database and having to apply “hacks” to get it to do what you need it to do – which makes for messy databases and performance problems down the line. Take the time to plan the structure properly in the beginning and you’ll have a smooth running database built for scale and flexibility.
2. Scattered data
If your database has related data scattered across several tables, which requires any changes to be made in various different places, you are exposed to unnecessary risk. The danger is that you may end up with partial records, where data is present in one table but missing in another.
3. Lack of systematic testing
Database testing should ideally be carried out as a systematic, standardised procedure. Many organisations limit their testing to a basic system acceptance test, where the end user runs some of their day-to-day functions and approves the system based on their experience. Deep system testing, however, will allow you to fully examine the quality of the data and the implementation. This will help give you a database that is faster and more reliable.
4. Hard deletes
Do you often delete data records? Although it can sometimes be useful to do a hard delete of data, it can also be risky. Many database administrators will know the frustration of having to restore a database to a separate server or sift through logs to try and salvage data deleted by mistake. A better alternative could be to use a soft delete, where you instead simply mark a row as inactive and use filters to remove it from view.
5. Bad use of primary keys
The primary key is the unique identifier for a particular record. In a well-developed database, this value should have no actual correlation to the data in the row. The value should be assigned randomly or sequentially. Still, many business databases allow this value to be a calculation or concatenation of relative or dynamic data. This can cause all sorts of trouble if the data needs to be moved to a different location.
6. Unnecessary complexity
If you have a database that is highly complex or has been over-engineered with the help of hacks or tricks, you may end up in a vulnerable position. Recording and storing critical data in a convoluted way, when it can be done much simpler, can make you overly dependent on those who have been involved in building it. Other people may struggle to fully understand the complexity and make sense of it.
7. Overuse of stored procedures
A stored procedure is a set of SQL statements that are designed to carry out a specific task which can then be shared across different programs. Historically, these procedures were used frequently. However, in a modern database they are often considered unnecessary as other techniques such as ORMs (Object-relational Mapping) are much more flexible and easy to use. In addition, stored procedures are generally seen as a nightmare to maintain. At the very least, we would recommend making sure a new stored procedure is absolutely necessary before creating one.
8. Poor indexing
One typical reason why your database is running slow could be that it is spending too much time running queries that scan through your tables row by row. For large databases, it can make sense to instead introduce “index scanning”, where your database browses an index rather than individual rows of data. By optimising your queries you can often speed up results dramatically.
9. Bad naming conventions
There are many areas of business where file names and terminology are important, but this is particularly true in databases. The person who designs the database may not be the same person who has to manage it five years later, so it’s important to make sure that columns and categories follow a set pattern and are clear, simple and descriptive.
One thing to avoid is the use of spaces and quoted identifiers in column names – such as “Item Number” – as this only makes it unnecessarily complicated for the user who creates the code.
10. Lack of security patches and fixes
Databases are often targeted by hacker attacks, and should be kept as well protected as possible. This isn’t just in the interest of performance, but to avoid data loss and leakage as well. Many businesses fail to keep on top of their systems with regular patches and fixes, which leaves their databases vulnerable to security threats.