Best Practices: Does Professional Database exists?

database
Database @Image courtesy of ddpavumba/ FreeDigitalPhotos.net

Best Practices: Does Professional Database exists?

Welcome to CodeSpread!

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 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 few pitfalls and following some guidelines but still, these are only guidelines and not the 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!

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More from CodeSpread:

  • Agile Encounters UnpredictabilityAgile Encounters UnpredictabilityWhat is Agile? Wikipedia says “Agile software development is a group of software development methods based on iterative and incremental development, where requirements and solutions evolve through...
  • How to create database with SQL express bundled with Visual Studio?How to create database with SQL express bundled with Visual Studio?Yes, Visual studio comes bundled with a copy of SQL express edition which gets installed during visual studio installation. These steps are required to create database with SQL express bundled w...
  • Few lines about Static code analysisFew lines about Static code analysisStatic code analysis We divide the term into two parts: Static+code analysis. Static in programming can be referred to as non-running/non-dynamic and code analysis is a process where the code is...
  • Lean in ITLean in ITWikipedia says ‘Lean IT is the extension of lean manufacturing and lean services principles to the development and management of information technology (IT) products and services.’ What are these ...
  • Scrum is AgileScrum is AgileIn our last article,Agile Part 1, we have explained basics of Agile and now we are sharing an article on SCRUM What is SCRUM? Wikipedia says “Scrum is an iterative and incremental agile software ...
  • Freeze Business Logic Layer.Freeze Business Logic Layer.We regularly work on three-layer architecture for application development and always use some standard data layer and UI layer guidelines for design but what we always ignore is business logic laye...
  • User Interface DesignUser Interface DesignWhat is a User Interface? Webopedia says “Abbreviated UI, the junction between a user and a computer program. An interface is a set of commands or menus through which a user communicates with a pr...
  • Risk Management in IT: Another step for QualityRisk Management in IT: Another step for QualityWhat is Risk? Risk is an uncertainty. Here, this pertains to a situation where It is difficult to decide that whether a particular event will occur or not. But if it occurs then what impact it wil...