Click here to Skip to main content
13,513,137 members
Click here to Skip to main content
Add your own
alternative version

Tagged as



3 bookmarked
Posted 19 Dec 2012

Best Practices: Do Professional Databases Exist?

, 19 Dec 2012
Rate this:
Please Sign up or sign in to vote.
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.

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:
    • 1st Normal Form
      • Primary key required
      • Each cell has a single value
      • Non-Primary key values are dependent on the primary key
    • 2nd Normal Form
      • All 1st Normal Form conditions are met
      • Partial dependencies are removed
    • 3rd Normal Form
      • All 2nd 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!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Himanshu DS
Web Developer
India India
I am a regular developer working on c#,,sql,cms,digital marketing related sites. Through my blog, I am showing the non-technical part of our daily technical terms and processes which can be used to describe it to a layman.Sometimes I write basic technical posts also.

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180417.1 | Last Updated 19 Dec 2012
Article Copyright 2012 by Himanshu DS
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid