Database Answers Evolution
  Welcome to our 'Cradle to the Grave' Tutorial
 

Back to the Home Page

1) INTRODUCTION :
This Tutorial is for beginners to Data Modelling.

Topics covered in this Tutorial include :-
  1. Creating Entities
  2. Primary Keys
  3. Foreign Keys
  4. 1-to-Many Relationships
  5. Hierarchies
  6. Inheritance
  7. Reference Data

I have used the Dezign Data Modelling Tool to create the Data Models in this Tutorial.
I chose Datanamic because it is powerful, flexible and affordable.

  • At the end of this Tutorial, we will have designed a number of Logical Data Models, which are commonly referred as Entity-Relationship Diagrams, or 'ERD'
    On July 4th. I added Conceptual Models to make things easier to understand.
    I created these in colour in Microsoft Word and their purpose is to be more 'user friendly'.

    If you are completely new to Data modelling, I recommend you go through this Tutorial and just look at these coloured Conceptual Models until you understand what is going on.

    Then look at the Logical Data Models and see how they compare.
    I hope you find this Tutorial interesting and helpful.
    Please email me and let me know.

  • 2) FROM THE CRADLE
    Topics covered include :-
  • Entities
  • Primary Keys

    The approach I have chosen for this Tutorial is to cover the Life span of a typical human being, from the cradle to the grave.
    This allows me to trace the increasing complexity in life and match it to an increasing complexity in Logical Data Models.
  • The first task is to decide on the scope of our Data Model.
  • My Approach has three Steps :-
    1. Establish the Scope of the Data Model
    2. Identify the 'Things of Interest' that are within the Scope, These will be called Entities.
    3. Determine the Relationships between them.

    DECIDING THE SCOPE OF OUR DATA MODEL
  • I have decided that the Scope is 'From the Cradle to the Grave'.
  • This will include childhood, teenage years, becoming a student, getting a job, getting married, getting sick, and finally dying
  • Therefore, anything outside this scope are not 'Things of Interest'.
  • Me


    From the Cradle
    Cradle to the Grave - Conceptual Model Number 2 Cradle to the Grave - Model Number 2
    3) ME AND MOMMY
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships

    How to create an Entity in Dezign
    1. Right-click on a blank area in the diagram
    2. From the drop-down list, choose Insert and Entity
    3. Check the 'PK' box for the Primary Key attribute,
    which will usually be the first one on the Entity.
    4. Click on Close to save the results.
  • 4) ME, MOMMY AND MY MEALS :
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Reference Data
    1. At this Stage, we show only the Entities with no Relationships and minimum Attributes. and specify only the Primary Key and one 'details' field that will be replaced later on.
    2. The Primary Key field(s) should always be first.
    3. You will notice that the first field in the Customers_version2 Table is the Customer_id.
    4. It has a 'PK' symbol beside it, which indicates that it is the Primary Key for the Table.
    5. The Primary Key is very important and is the way that we can recognise each individual record in the Table.
    Creating a Primary Key in Dezign
    1. Right-click on the Entity
    2. Choose Attributes
    3. Check the 'PK' box for the Primary Key attribute, which will usually be the first one on the Entity.
    4. Click on Close to save the results.
  • Me        Cradle to the Grave - Model Number 3
    Me        Children's Playgroup
    5) ME AND THE PLAYGROUP :
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Here we have added the Relationships between the Entities.
    1. When this Primary Key is used in another Table, it is referred to as a'Foreign Key'.
    2. We can see a good example in this diagram, where the customer_id appears in the Customers_Payment_Methods Table as a Foreign Key.
    3. This is shown with an 'FK' symbol beside it

  • Mandatory Key Fields
    1. A Foreign Key is usually mandatory, in other words, a value for a customer_id in the Customers_Payment_Methods Table must correspond to an actual value of the customer_id in the Customers_Version_1 Table.
    2. This is shown in the diagram by the short straight line at the end of the dotted line close to the Customers Table.
    Foreign Keys in Dezign
    1. Foreign Keys are created automatically when you make a Relationship between two Entities.
    2. I recommend that you move the field up in the Entity so that it takes it place alphabetically among the Key fields.
    To do this, right-click on the Entity choose the Attributes option, then click on the up or down arrow on the right-hand side.
  • 6) I GO TO CHURCH SUNDAY SCHOOL :
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Addresses
  • One-to-Many Relationships
    1. A Customer can have more than one Payment_Method, for example, American Express or Cash.
    2. In other words, we would say that the Relationship is optional at the Customers_Payment_Methods Table end.
    3. This is shown by the symbol that has three small lines at that end of the Relationship dotted line, which is referred to as Crow's Feet.

  • Optional Key Fields
    1. Not every Customer will have a Payment_Method. In general, they would but we need to allow for situations where Customers change their minds and don't buy anything.
    2. In other words, we would say that the Relationship is optional at the Customers_Payment_Methods Table end.
    3. This is shown by the little 'O' at that end of the Relationship dotted line.
  • Me       


    Church Sunday Schools (Made smaller for this page)
    Me       


    Student Accommodation
    7) I MOVE INTO STUDENT ACCOMMODATION :
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Reference Data
    At this Stage, we add Reference Data.
  • This diagram shows how the hierarchies of Products and Product Types that we have just discussed are shown in our Entity-Relationship Diagram.


  • Rabbits Ears
  • You will notice that the table called 'Product_Types_v1' has a dotted line coming out on the right-hand side and going back in again on the top-right corner.
  • Data Analysts call this a Reflexive Relationship, or informally, simply 'Rabbits Ears'.
  • In plain English, we would say that the Table is joined to itself and it means that a record in this Table can be related to another record in the Table.
  • This approach is how we handle the situation where each Product can be in a hierarchy and related to another Product.
  • For example, a Product called Panini could be in a Product Sub-Category called 'Miscelleneous Sandwiches' which could be a higher Product Category called 'Cold Food', which itself could be in a higher Product Super-Category called simply 'Food'.
  • Next time you go into Starbucks, take a look at the borad behind the counter and try to decide how you design the Products area of the Data Model.
  • You should pay special attention to the little 'zeros' at each end of the dotted line.
  • These are how we implement the fact that the 'Parent Product Type Code' is optional, because the highest level will not have a Parent.
  • 8) STUDENT ASSESSMENTS !!! :
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships
  • Many-to-Many Relationships
  • Addresses
  • Food and Drink are specific examples of the more general Thing called a Product.
  • They inherit some common attributes from the Product, and also have some of their own.
  • For example, Food can contain Nuts but Drink do not contain nuts, but both have a Product Name.


  • The unusual symbol in the middle of the diagram, composed of a circle with two small lines underneath it is how Inheritance is shown using the particular Data Modelling Tool that I am using,which is called Dezign.
  • Inheritance is a very important topic when you are creating a Data Model.
  • In plain English, we would say that Inheritance occurs where a Parent-Child relationship exists between Things of Interest (or Entities).
  • You can ask the simple 'Is-a' question - in this case, if we ask 'Is a Book a Product' then clearly the answer is 'Yes' so we think there is an Inheritance relationship between them.
  • In the example of Inheritance shown in this diagram, we can see that all Products have Names and Descriptions. Therefore, Books, Food and Drink will inherit these characteristics from the parent Product.
  • However, each type of Product will have specific characteristics that it does not share with other types of Products. For example, Books have ISBNs and Authors, but Food and Drink do not.
  • One of the important things in your Data Model is to be sure you have identified all the Inheritance relationships.
  • However, from many years of experince as a DBA, I should point out that relationship is often blurred in a real physical Database because it can be clumsy to implement.
  • I sometimes find myself showing Inheritance in a Logical Data Model which then disappears when I design the Physical Database, which is what ultimately becomes the Database.
  • Me       


    Student Assessments
    Me       


    Social Networking Sites - eg Facebook
    9) I JOIN FACEBOOK :
    Topics covered include :-
  • Entities
  • Foreign Keys
  • Primary Keys
  • One-to-Many Relationships
  • Many-to-Many Relationships
    • This diagram shows Address Types, which are an example of Reference Data.
    • This kind of data has the following characteristics :-
      1. it doesn't change very much
      2. it has a relatively small number of values, usually less than a few dozen and never more than a few hundred.
      3. Therefore we can show it with a Code as a Primary Key.
      4. Data in Reference Data Tables can be used to populate drop-down lists for Users to select from.
      5. In this way, it is used to ensure that all new data is valid.
    • Standards
      • In the Address Table, you will see a field called 'iso_country_codes'.
      • iso stands for the 'International Standards Organisation'.
      • Where possible, it's always good to use national or international standards.

        Customer Addresses
      • This is a general and flexible approach to handling Addresses in our Data Model.
      • We have a separate Address Table, which allows us to have more than one Address for any Customer very easily.
      • This design also has some other benefits :-
        1. We can accomodate more than one person at the same Address. We need to do this because different members of a family may sign-up separately with Amazon.
        2. With a separate table of Addresses, we can easily use commercial software to validate our Addresses. To find this kind of software, simply Google for "Address Validation Software". I have used QAS with great success in the past. With this approach, we can always be sure that we have 100% good Address data in our Database.
  • 10) I JOIN THE SWIMMING CLUB :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data (eg Address Types and Payment Methods)
    Swimming Club       


    Swimming Clubs Model
    Me       


    Traffic Cops and Tickets Model
    11) I GET A TICKET FROM A TRAFFIC COP :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data, eg Document Types and Record Status
    12) I GET MARRIED :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data
  • This Model was created using a different Data Modelling Tool, called ERWin from Computer Associates.
  • It shows that if you are familiar with the underlying principles that you will be able to understand and ERD.
  • Me       


    Weddings Model
    Me       


    Baseball Umpire Scheduling Model
    13) I BECOME A BASEBALL UMPIRE :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data, eg Division Types
    14) I GO TO HOSPITAL :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data
    Me       


    Hospital Admissions Model
    Me       


    Funeral Homes Model
    15) TO THE GRAVE :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data
    16) EVENTS IN MY LIFE :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Reference Data
    Me       


    My Life Data Model
    Me       


    My Work Data Model
    17) EVENTS IN MY WORK :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Hierarchies (eg Organisations)
      6. Inheritance (from Information_Catalogue to Customer_Data, Invoices, Contracts and Orders.
      7. Reference Data eg Status_Codes
    18) SUMMARY OF TOPICS IN THIS TUTORIAL :
      Topics covered :-
      1. Primary Keys
      2. Foreign Keys
      3. One-to-Many Relationships
      4. Many-to-Many Relationships
      5. Hierarchies
      6. Inheritance
      7. Reference Data
  • If you would like to see other Topics included, please let me know. Tutorial.
  • Me        Top-Level Data Model

    19) PLEASE EMAIL ME
  • I hope you have found this Tutorial interesting and useful.
  • Please email me with your questions or suggestions so I can improve it.

  • If you want to try the Dezign Data Modelling Tool, you can download it from the Datanamic Web Site.

    Good luck with creating your first Data Model !!!

    Barry Williams
    Principal Consultant
    Database Answers Ltd.
    London, England


  • Home Ask a Question Best Practice Communities Contact us Data Models Search Site Map

    © DatabaseAnswers.org 2015