Challenging Situations in Data Modeling

Challenges & Solutions in Data Modeling:

Anyone can Learn Data Modeling Concepts by reading some books or going through online or by getting some help from experts in the field. But practically, while working with a particular Data Model, there may arise many challenging situations where one has to think about various possibilities and requirements, before imposing a cardinality rule or before creating a relationship. Will it be sufficient if I go with one additional entity or I should create one lookup in order to satisfy the Business Requirement? Will the current data structures be hold good for any future expansions and modifications?

So many challenging situations may arise during the course of one Data Modeling Life Cycle. This page is entirely for that purpose which makes Data Modeler to be to think ahead and decide on what rule to apply in order to create the right Data Model.

We will come up with many difficult situations and present you with some practical based scenarios and post it on this page on the daily or weekly basis. Data Modelers make a note of this page and use it as a reference or an assignment to improve your skills in the Data Modeling Field.

Challenging Situations in Data Modeling from the Client End:
  • No documents or fewer documents with less information which explains the business process and business rules.
  • Less knowledge and no proper explanation from SME or BAs.
  • User requirements and demands are more.
  • No proper plan in the Enterprise Architecture.
Challenging Situations in Data Modeling from the Data Modeler’s Perspective:
  • Not able to understand the business requirements.
  • Understanding the business requirements but not able to predict the entities required for this project.
  • Understanding the entities required but not able to place the attributes properly in the entity.
  • Placing the attributes in entities but not able to create identifying relationships or non-identifying relationships.
  • Able to create identifying and non-identifying relationships but not sure whether ZeroOrOne to ZeroOneorMore, One to ZeroOneorMore.

How the change in the business rules affects the data model?

Original Business Rule 1:

  • Employee’s present manager to whom he reports must be tracked.
  • An entity called Employee is created and by using Self Referential Integrity and the role name, Manager is added.

Business Rule 1 Change:

  • Employee’s previous manager, present manager and future managers are to be tracked.

Original Business Rule 2:

  • Employees current residential address must be tracked.
  • An entity called address is created and you connect that address with employee entity.

Business Rule 2 Change:

Employees previous/current/future residential address must be tracked.

Here how these Business Rules(1 & 2) affects the current Data Model and as a data modeler, how will you implement these changes? What is the best way to do it?

Business Rule 3:

If there are three definite values for a column, and if you are sure that these values never change over time then what approach you will follow? – a Lookup or a Check Constraint?

Example: In URLA 1003 form provided by Fannie Mae, Page No.5, Section No. 4 one can see Purchase, Refinance and Other as the values for the field “Loan Purpose”. Should we create a lookup for these 3 values or will it be better if we create an identifier and impose a check constraint on these 3 values.

Business Rule 4:

How will you implement a Candidate Key – By creating it as a Primary Key or an Unique Constraint?

Example: Social Security Number is always a Candidate Key and whether this should be created as a Primary Key in a table or a stand alone column with Unique Constraint imposed on it.


Reach US!!!


  • We provide online training in advanced OLTP Data Modeling and DIMENSIONAL Data Modeling.
  • We also teach the data structures with Data Analytics Software “R”.
  • We provide online Data Modeling Project Support when you get struck with projects that you are involved.
  • We can analyse your Business Requirements, understand and suggest solutions to create OLTP Data Models and Dimensional Data models.
  • We provide Data Modeling Interview Preparation Sessions with a lot of Data Modeling Interview Questions/Answers, which will help you to clear any interview.

If you are interested, please reach us at or 91-9080157239

Leave a Reply

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