Best Practices: Do Professional Databases Exist?

What is Database?

Database is a structured set of data, which facilitates storage, search and retrieval. Databases revolve around tables and relationships between them. Imagine you have a truck load full of apples, each marked with a number. If these apples are not arranged as per their numbers, then it would be difficult to find the requested apple having a particular number in less time. Silly example? but silly works.

What is Database Design?

We know that a database is a structured set of data, but what is this structure? This is the question we try to answer by designing our database. Designing a database is a mapping between user’s requirements and logical implementation of these requirements. For example: If user[employee] wants his name and his manager’s name to be stored in the database, then the designer will think of only ONE table having column ManagerID referring to EmployeeID.

EmployeeID | EmployeeName | ManagerID

If an incorrect structure is imagined, then we might see two tables, one for employee and other for manager.

It is an important decision as once implemented, it would be difficult to go back and change the design. A well-designed database is what we should aim for.

Is There Any Guideline to Achieve Professional Database?

Are we looking to reinvent the wheel here? My answer would be ‘No’. I believe in accepting the fact that old methodologies and best practices are good enough to achieve excellence. Same applies to the process ‘development of professional database’.

As I said in my  last article, quality is an iterative process to achieve a degree of optimization. When we think of DB design, we try to use all the best practices and if we found out that it is still not the best design, we follow the same iterative process to achieve better performance of our DB. There are few guidelines which might be useful for one set of DB and discarded by others, as these are outcomes of experience and not a rule-set. That means, No debates, please!

What are the Pitfalls?

• Everything begins from requirement gathering so if we have not done proper analysis of requirements, then there is a high chance that our database design will get affected
• Partial clarity on business requirements
• Did not identify the nature of application as it might be CRUD based or Analytics based
• Did not identify the duplicate content before development starts
• There might be disconnected data, but always account for this data as it might create redundancy
• Incorrect level of Normalization might create either an over normalized database or under normalized database
• Ugly Naming conventions which impacts readability of database
• Avoiding Data Integrity can cause harm
• No Data Security
• Poor Performance

Any Solution?

The best solution is to take care of the above pitfalls. We have the problems which are common in database design but we don’t have enough solutions to counter each problem. But still, we can achieve an optimum design by following a few guidelines.

• Try to think of the high level view and not individual fields.
• Think how your application is going to interact with database.
• Prepare entity-relationship diagram (ERD) to have a readable format.
  • The requirement analysis done can be transformed into an ERD (Entity Relationship Diagram) that is the data is organized into entities and relationships between them. So instead of going through a big document, we have a readable format.
• Use proper naming conventions. It improved readability. For example: EmpID will not be a choice in comparison to EmployeeID.
• Normalization is required, but check the application before introducing unnecessary level of normalization.
  • Database normalization is a set of guidelines for good database design. Normalization says ‘avoid redundant data’. We just have to follow this simple rule and Normalization will bring benefits like efficiency and maintainability to our database. The first three forms of normalization are:
  • 1^st Normal Form
    • Primary key required
    • Each cell has a single value
    • Non-Primary key values are dependent on the primary key
  • 2^nd Normal Form
    • All 1^st Normal Form conditions are met
    • Partial dependencies are removed
  • 3^rd Normal Form
    • All 2^nd Normal Form conditions are met
    • Transitive dependencies removed
• When we work on a database design, we should take care of any null checks, any validations, any string length checks, and constraints to improve data integrity.
• Avoid any kind of prefixes to Tables Names, Stored Procedure names or functions.
• Avoid large repositories, try to create small workable entities.
• Accommodate data security by introducing authentication and authorization.
• Use tools like Profiler extensively to keep a check on the performance. Keep few points in mind while developing database.
  • Use of indexes
  • Identify large tables and complex processes
  • Use Joins up to a limit

I want to conclude here by saying that, we can achieve a professional database design by avoiding a few pitfalls and following some guidelines, but still, these are only guidelines and not an exact rule-set which can guarantee a professional database. My only suggestion is that you increase your experience in database design which will help you to foresee the complete picture and surely help in creating a professional database. Good luck!