Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Best Practices: Does 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 revolves 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 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, that 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 accounts 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 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 , 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!

License

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

Share

About the Author

Himanshu DS
Web Developer CodeSpread.com
India India
I am a regular developer working on c#,asp.net,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.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 19 Dec 2012
Article Copyright 2012 by Himanshu DS
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid