((It is a collection of articles about database to repeat points. Thank you for your cooperation.))
Poorly designed databases are counterproductive to performance.
Yet, many companies are unaware of the little errors causing big problems.
These ten database design best practices will help you significantly increase your company’s database management and performance, create more productive search queries, and improve data quality.
Tip #1: Avoid Using Hints
Some prominent RDBMS vendors have spent countless hours and untold money to create the most sophisticated and efficient SQL engines. Hints explicitly restrict the RDBMS from utilizing the most efficient access path to the data. If a recommendation is needed, it is most likely a database/query design flaw that needs to be addressed.
Tip #2: Proper Datatypes
The benefits of using correct and proper datatypes for attributes within the database are two-fold:
- It ensures the correct type of data is added to the database. For example, 20010229 can’t be added to a DATE datatype attribute; this value could be added to an INTEGER datatype.
- It provides better insight into the UPDATE STATISTICS command. Storing ‘Dates’ as an INTEGER rather than a DATE datatype will result in the incorrect number of potential values available to the attribute. For example, there’s a potential of 100 deals between 20010101 and 20010201 for an INTEGER compared to only 31 for a DATE datatype.
Tip #3: Eliminate Unnecessary Attributes within Queries
To maximize performance, limit the attributes in the SELECT clause within the query to only the details necessary to satisfy the business requirement. Limiting the details reduces the amount of data transferred from the disk and allows for more valuable data per page of memory, resulting in more efficient query execution. Note: Never use the ‘*’ in the SELECT clause for production queries.
Tip #4: Think about Relationships
When designing new relations, it’s essential to consider the environment in which the link will be deployed (i.e., OLTP, Operational Data Store, Data Warehouse, etc.).
The following items are crucial to designing new or modifying existing relations:
- Levels of data normalization to be deployed;
- Consistent datatypes across links;
- Primary key and secondary index attributes and order;
- The cardinality of each attribute
Tip #5: Keep Statistics Updated
Statistics provide the SQL engine with knowledge and insights into the data. The OPTIMIZER utilizes the statistics gathered by the UPDATE STATISTICS process to determine the most efficient access path to the data. As data changes over time, so can the access paths into the data. Therefore, it’s important to keep statistics current and recompile programs frequently.
Tip #6: Avoid Table or Index Scans
One of the biggest wastes of system resources (i.e., disk, CPU, memory, etc.) is accessing data that a query will never utilize. Unnecessary scanning of data is generally the result of one of the following items:
- Missing predicates in a query
- Poorly structured query
- Non-keyed or non-indexed attributes utilized as a predicate
- Poorly structured compound key
- Outdated statistics resulting in an inefficient QEP (Query Execution Plan)
- Incomplete or poorly structured JOIN between relations
- Predicate used as a function
Tip #7: Limit Temporary Tables
A temporary table is a great feature of most SQL implementations that allows for an intermediate data set to be repeatedly used by a query or stored procedure. However, several reasons exist to limit or eliminate temporary tables: Advances in RDBMS engines can now provide this functionality automatically at run time.
The extra I/O operations negatively impact query execution time to READ the initial data, WRITE the data to the temporary table, READ data from the temporary table, and drop the temporary table. Query execution is suspended until the temporary table can be populated.
Tip #8: Limit the Utilization of the TOP Function
There is a common misconception that when the TOP condition has been satisfied, query execution terminates. Unfortunately, this is not the case. An RDBMS engine requires a materialized intermediate result set before evaluating the TOP condition.
Tip #9: The ORDER BY Clause is Mandatory in a Query Where Sorted Data is Mandatory
There are many reasons for processing data in a specific order; however, limiting the ORDER BY clause to queries where the business requirements call for the data to be in a particular directive is imperative. An alternative might be to structure, via primary key and an Index, in a manner that results in the data in the order needed to satisfy the business requirements without utilizing a SORT operation.
Tip #10: Use ‘Cover’ Indexes Where Possible
The advantage of utilizing multiple indexes is gaining efficient access to data other than the primary key. However, most indexes are designed to result in a secondary READ of the base table to obtain all data required for the query. A cover index, by design, will include attributes that may not be utilized as predicates but to satisfy the data requirements of the question.
10 Tips and Tricks to Manage an Efficient Database
Databases are essential for businesses because they communicate information, track customer profiles and monitor product inventory. With this information, you can improve efficiency, make data-driven decisions and avoid redundancy. However, your database can only be as reliable as you make it. Below are ten tips and tricks to help you manage an efficient database.
1. Back up everything
Be diligent with your backups to prevent data loss. You can do this by backing up data to the cloud, saving it to an external hard drive, or putting it on a flash drive. We recommend backing up to the cloud because it’s safe, secure, and automated.
2. Prioritize data security
Data must be secured, both physically and virtually. You should also encrypt all data in case you are ever hacked. Invest in multi-layer protection, including firewalls, multi-factor authentication, anti-virus software, and regular security training.
3. Ensure compliance
Make sure you’re aware of the latest data rules and regulations. For example, the General Data Protection Regulation (GDPR) has strict requirements for collecting and using user data. If you fail to comply with these policies, you could face a hefty fine.
4. Create a data recovery plan
Even the best data security policies can fall victim to an attack. Create a data recovery plan to know what to do if disaster strikes. Your goal should include who is responsible for the data and the steps to follow in case of a breach.
5. Standardize data entry policies
Ensure everyone in the workplace is adequately trained in data entry. Even minor errors can prove detrimental when working with large data sets.
6. Collect only what you need
Just because you can collect data doesn’t mean you should. Only collect the information you need. This makes it easier to understand your data and comply with the latest privacy regulations.
7. Make your data accessible
Don’t make your database inaccessible to others. Users should have access to your database to use and apply the information you’re collecting.
8….But also be smart about granting access
Of course, you don’t want to grant access to your entire company. Be selective about who you give access to, and modify their permissions so they can only do certain things in the database.
9. Keep everything up to date
Keep your database updated by adding new data immediately, deleting no longer needed information, and normalizing your database (this avoids redundant data). Also, make sure you follow all updates.
10. Enlist help from a database expert
If you need help creating, managing, or updating your database, contact a database expert for help. Having these services on hand ensures you’re always using the best version of your database.