Skip to content

MS SQL

Database Modeling with Real Requirement and Scenario

Database modeling is one of the most challenging parts of a software development. It needs a significant amount of software development experience apart from domain expertise to design and model a database that is truly future proof – one which is flexible enough to accommodate the changing needs of a business.

Above all it requires loads of patience to understand the finer points of the client requirements. Over the years I feel that modeling a database is a gradual learning experience that can’t be taught in a few month courses, but it can be learnt only by experience – fail, learn, design! Repeat.

There is indeed no dearth of articles on this topic by many database gurus, so there’s no point in repeating the same over here. I would take a different approach based on some real requirement and scenario. After reading this you might get an idea of how to proceed with the database modeling at first place.

Suppose your client who is an owner of a Theme Park (Or a resort or any similar property) and wants to sale various packages online via her/his own booking engine on her/his portal. Suppose s/he has shared the following tabular structure containing following details. Now in first phase of development s/he simply requires an application where he can enter and retrieve data related to any of his packages.

Requirement

The above requirement might look a bit complicated to the database modeling beginners, but it is not so. Let us see how we can simplify it and make a simple database model.

Now before jumping on to designing the tables, let us first translate the finer points of above requirement in to simple tech-requirement.

  1. There can be various packages.
  2. One package can have multiple prices.
  3. Packages can have various terms and conditions.

What could be the future requirements?

  1. Client can introduce several new packages.
  2. Similarly s/he can discontinue an existing package.
  3. S/he can introduce a new Price Type.
  4. New terms and conditions can be added at package level.
  5. New terms and conditions can be added at price type level.
  6. S/he may like to know when a package was created/updated.
  7. S/he may like to know who created/modified the package.

From above you can easily see that you’ve only 3 points that are obviously understood from the requirement and surprisingly there are be at least 7 points that are almost hidden. Anyways, the above requirement can be accommodated in the following 2 tables:

Table-Structure

Technically speaking this was an example of modeling a One-to-Many relationship in database. If you understood this you can model most of the scenarios as this is the most frequently occurring scenario.

DataEntryTable

Soon, I’ll post other scenarios too like Many-to-Many relationship with an example. How was it? Please share your comment.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)