Relational Data Modeling Example

The sample source data shown in the table below provides the information about employees, their residential state, county, city and their employer names and manager names. It also describes employees working for an “American Bank” that has got many branches in several states. From data modeler point of view, analysis of the source data raises following questions.

  • How to group and organize the data?
  • How to avoid de-normalization since employee’s residential data like state name, county Name, city Name are repeated in most of the records.
  • What sort of relationship is between employer and employee?
  • What sort of relationship is between the employee and state, city, county?

Sample Source Data:

State NameCounty NameCity NameEmp First NameEmp Last NameEmp Full NameManager NameEmployer NameDate TimeStamp
New YorkShelbyManhattanPaulYoungPaul YoungAmerican Bank of New York1/1/2005 11:23:31 AM
FloridaJeffersonPanama CityChrisDavisChris DavisPaul YoungAmerican Bank of Florida1/1/2005 11:23:31 AM
CaliforniaMontgomerySan HoseLouisJohnsonLouis JohnsonPaul YoungAmerican Bank of California1/1/2005 11:23:31 AM
New JerseyHudsonJersey CitySamMathewSam MathewPaul YoungAmerican Bank of New Jersey1/1/2005 11:23:31 AM
New YorkShelbyManhattanNancyRobinsonNancy RobinsonPaul YoungAmerican Bank of New York1/1/2005 11:23:31 AM
FloridaJeffersonPanama CitySheelaShellumSheela ShellumChris DavisAmerican Bank of Florida1/1/2005 11:23:31 AM
CaliforniaMontgomeryShelbyJeffBillJeff BillLouis JohnsonAmerican Bank of California1/1/2005 11:23:31 AM
New JerseyHudsonJersey CityJohnBurrellJohn BurrellSam MathewAmerican Bank of New Jersey1/1/2005 11:23:31 AM

In the next page, we will discuss how to resolve these problems in order to design a good relational data model.

Next: Relational Data Modeling Example РPart 2

(Visited 2,921 times, 1 visits today)

One comment

  • Hi, nice article. But I slightly disagree with the reason you say people are modeling. I think your questions can all be placed under one header, which is how to model the data in such a way that the business rules can be enforced through the model. If there are no business rules to enforce, the logical model is a single big table.

    Normalization is done to create a situation where updates can be forced to be consistent by having only one copy of an entity, but if you don’t want or need that business rule, no normalization is necessary.

    Sometimes you want to model for technical reasons (e.g. create a Kimball model in order to reduce I/O and improve join performance) and in that case, denormalization is actually desirable.

Leave a Reply

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