These database best practices
will save you countless hours and headaches
Database best practices are rarely made for the software, but rather for the human. By using them, you’ll simplify everyone’s job. It makes the database easier to read and understand. You can adapt them the way you seem fit for your needs, just remember to follow rule #0:
The Rule of rules: Be consistent
You can break any rule, but you HAVE to follow this one.The only reason best practices exist is to make thing easier. If you decide to things differently, make sure you always do it the same way.
Beginners database best practices
Rule #1 – No grammatical errors or mistype when naming objects
Make sure there is no grammatical error in your object names (tables, queries, etc.). You can lose a lot of time wondering why you can’t find the view “qEmployees” because you named it “vEmployees”.
Rule #2 – Never put special characters or spaces in your table’s and columns’ name
Never use special characters in your table name. Just standard English alphabet. Also if you speak another language, it is recommended not to use characters that don’t exist in English (é, ñ, ç, etc.).
If you want to use a “space” in an object name, use an underscore instead. As in “Example_table” or “Customers_Divisions”.
Rule #3 – A primary key is always at the beginning of a table
To make it easier to read, the first field of a table is always the primary key.
Rule #4 – Put foreign keys at the end of the table
It’s easier to read: primary key at the beginning, normal fields in the middle and the foreign keys at the end.
The only exception is when the foreign key is also part of the primary key, then it’ll be at the beginning of a table
Rule #5 – Only use numbers for primary keys
Using a text field as primary key will slow down the database since indexes are faster on a numeric field than a text field.
Rule #6 – Don’t use composite primary keys
Not every database administrator will agree with this one, but I do
prefer using automatic numbering as a primary key instead of composite primary keys. It’s easier to manage and eliminates the burden of having too many double keys: sometimes when you create a middle table that has two parents with double-key, you end up with a middle table that has a primary key composed of 4 fields.
Rule #7: Always use primary key #0 as the
“nothing” value
In every table, always use the primary key #0 as a “nothing” value. So when you see a foreign key set to “0”, you know that field is empty.
Rule #8 – Always have a default value of 0 on a foreign key
Having a NULL value in a foreign key field is bad practices. Set the foreign key default value to 0. Make sure you follow rule #7 when you do that.
Intermediate database best practices
Rule #9 – Always use Cascade Update
Data integrity is crucial in a database. Use cascade update to eliminate any risk of data corruption. If a parent’s primary changes, all the related foreign key will be updated as well.
Theoretically, this shouldn’t happen, but it, you’ll be glad the foreign keys are updated when a bug happens and you don’t have to update the foreign keys manually.
Rule #10 – Never use Cascade Delete
The advantage of cascade delete is that it never creates an orphan. Let me explain. with the Cascade Delete option on, if you delete “Customer #6”, then all the purchase order associated with #6 will be deleted. After that, each invoice related to each PO will be deleted, etc.
If it was caused by a bug, manually restoring all this data will give you a long-lasting headache. Anyway you should never delete a record (see next rule).
Rule #11 – Never delete a record
Data should never be deleted in a database. Instead:
- In each table, create a Boolean column named “Deleted” and set the default value to false (not deleted, active)
- when the row is deleted by a user, instead of actually deleting the row from the database, set the value to true
- when creating views, make sure you only show active records (records where deleted is set to false)
That way, the users will think those rows are deleted, but you still keep your data for future reference. And the DBA will be able to easily recover the data if the row was deleted by mistake.
Rule #12 – Always use views to query a table
When developing a software, always query the database through views. Never directly query the table.
In modern DBMS, views are loaded into memory, so when the users query the database, it’s much faster to extract data. Plus, at the creation of the query, the DBMS will automatically create indexes.
Rule #13 – Do not use NULL values
in your tables
NULL values are not really a value. It’s not an empty string, it’s not zero. Plus they are handled differently from a DBMS to another. Don’t use them whenever you can, it easier. Instead, set the field default value to zero or to an empty string.
Rule #14 – Use the right data type for your fields
Choosing the exact data type of every field is important. It will ensure data integrity and on the long run, it will significantly increase database performance.
Rule #15 – Don’t use “SELECT * FROM”
When querying a table, define all the columns you need instead of using “SELECT * FROM”. It will increase database performance.
Rule #16 – Backup daily
Backup you database daily and keep at least one
backup a week out of the office.
Advanced database best practices
The advanced database best practices are more about database performance. These rules will make a difference for larger systems with many users.
Rule #17 – Always install the DBMS on
a stand-alone server
A database server should always be on a stand-alone server. Don’t install the DMBS on a shared computer, unless it’s a small system or there are very few users.
This way, you will be able to optimize your server’s performance. If you’re installing other processes on the same server as your database management system and experiment loss of performance, you won’t be able to fully control the use of processing power. See the article on two and three tiers architecture for more details on software architecture and the database layer.
Rule #18: Do not store files in a database, use the file system
The job of a file system is to store files and the database’s job is
to store data.
When using files – word, excel, jpeg, gif – with databases, do not store the file in the database (using BLOBs). Store them on the file system.
It is better optimized than a database to manage files. Performance will be increased and you’ll need less storage space..
Rule #19 – Put the log and database files on separate drives
To increase performance, always put your log and database files on separate
drives. It will increase the read and write speed. The database files should be put on RAID 5 drives while the log files should be on a mirror drive.
Log files are “write-intensive” while databases files are “read intensive”. Putting them on different drives will increase performance and diminish the chances of bottlenecks.
Rule #20: Don’t use row by row editing in a stored procedure
Stored procedures allow row-by-row editing, but it should not be used. It is generally considered bad programming. Row-by-row editing may seriously compromise the database performance.
You should be able to find a way to do the same things with the standard SQL commands.
Rule #21: Never compress a database
Never compress a database or a database hard-drive. Compression slows down a system and storage is cheap, buy new hard-drives.
Rule #22: Do not encrypt a database
Apply rigorous security measures and you won’t need to slow down your systems with encryption.
Final note on database best practices
Remember, the only rule you have to follow is the one that says you have to be consistent. Always do the same thing!
More database best practices will be added from time to time. If you think we forgot one, please tell us through the contact us page 🙂
Home > ERP Selection > Database Evaluation > Database Best Practices
