Five Database Design Performance Tips For OLTP Systems

If you work with databases, you probably already know that there are many different kinds of database designs. Depending on the type of database you are working with, there are many different things you need to look at in order to thoroughly analyze database schema designs. Here are five database design performance tips that you should consider when working with OLTP Systems:

1. Is normalization apparent in the database design? A good starting point when it comes to critically evaluating database design is checking if database normalization is present – to be particular, in 3NF or Third Normal Form (or even BCNF). There is a reason why normalization processes are always endorsed – because it is highly effective in resolving insertion, updating and deletion anomalies as well as support the application’s data integrity. If there are too many tables with the exact same unique keys, you will know that you have over normalized things (in 4NF or 5NF) – which will kill the database’s performance.

2. Solid database design leverages, filters and accesses through natural keys based on aspects of the business it serves. It’s a good idea to make use of natural keys in the database’s design. There are often times (especially when it comes to the newer type of NoSQL databases or ORM products for web development) when a SEQUENCE, ROWID, or IDENTITY column gets used or is required. Such keys limit database integration through the business environment. In an indirect way, they are detrimental to the database’s performance.

3. Do you have good column definitions in the database? The names and data types of columns are used, provided that the database remains active (so as to confirm the right names, abbreviations, and also short standard names used as column names). Make sure all your columns do not appear as single data types like XML, CHAR, or VARCHAR, as it fails to reflect the data’s business diversity. While column definitions are easy to change, take care to select numeric types and value ranges so that the right SMALLINT, BIGINT, INT, or DECIMAL are used. Well-defined tables that have solid numeric and business-centric keys are always better.

4. Is NULLable present across all columns? Analyze, evaluate, and prototype everything for good business applications. The presence of good default values and limiting the scope of the values is the best thing to do, performance-wise and for robust application logic. NULLable columns can only be considered a good thing when your data is not known or when it does not have any values yet. For example, a person’s death date is one type of NULLable column since it is not known unless you directly find out that the person is indeed dead already. Ensuring that your database design has a good representation of known data and uses a small number of NULLable columns is the best route to go.

5. Finally, were you able to leverage the right amount of RI or Referential Integrity? RI is defined within database schema definitions simply because it implements business policies, and is more efficient for the multiple applications that enforce it. In contrast, a database designed with referential integrity in mind will still be there many years later to validate all data relationships. It is during the development phase of applications that RI needs to have definition right within database schemas so that coders will be able to understand it and be aware of how to minimize RI overhead efficiently. This is done by referencing as well as performing the operations in the right RI sequence.

The RI ought to be developed within the business application’s correct depth as well as the different kinds of transactions found within the schema. Solid RI database structures can be made within 5-10 levels. Going over this will lead to performance issues, which will go back to the over-normalization of the table design.

We hope this post will help you make better decisions when designing your next OLTP database. These five database design performance tips are quite simple, but are great starting point for the next time that you need to develop a new database.