Database Answers Panoramic View of Windsor Castle
Home Ask a Question Best Practice Careers Clients Data Models myDBA Search Site Map Contact Us
  Dimple and Toby Visit Windsor Castle

1) Tutorial on Data Modelling for Young People :
In this Tutorial, we follow two young visitors to Windsor Castle in England.
An iPhone Version is being created.

Our visitors are Dimple, a 10-year old girl, who likes sightseeing and icecream
and Toby, Dimple's 12-year-old brother, who likes sightseeing and databases.

[Dimple] : Toby, It's great being in London which is so exciting and buzzing. Dimple, (a 10-year old girl, who likes sightseeing and icecream)

Toby (Dimple's 12-year-old brother, who likes sightseeing and databases) [Toby] : I'm glad you like it, Dimple. What would you like to do today ?
[Dimple] : Toby, we have seen Buckingham Palace, where the Queen of England lives, and now I'd like to visit Windsor Castle, because it's one of the most popular tourist attractions in England, and it's easy to get to from London.

[Toby] : OK. Let's go ...
Buckingham Palace, London
[Dimple] Wow, Toby, there are so many Banks, Cafes, Pubs, Restaurants, Shops, Wine Bars and Hospitals !!!

There is even a Castle, as well as a Royal Park with Deer.

[Toby] : Yes, let's start thinking about our Data Model.

The other thing that we see when we look around is people - lots of people.
Shops with the Castle in the background

[Dimple] : How do we get started ?

[Toby] : Well, we know that we have People and Places.
The simplest start is to call all these places Establishments.
Then we simply have different kinds of Establishments.
And we have people - local people, visitors, people working here, people here on business and so on.

[Dimple] : Hmmm - so how do we translate what we know to help us get started with our Data Model !!!
People and Establishments

[Dimple] : Toby, I am one of these People so how I am I going to identify myself from everybody else.

[Toby] : We will give everybody a Unique Identifier and every Establishment its own unique Identifier.
When we use these we call them Primary Keys, and show them in the diagram with a PK on the left-hand side.

[Dimple] : That sounds good, Toby, but I don't know what it means.

[Toby] : Well, Dimple, let's look at how we use these Identifiers ...
People and Establishments with Identifiers

[Toby] : Dimple, now we can add some interesting details because we know that one person can visit many Establishments.
We also know that one Establishment is visited by many Visitors.
Then we call this a Many-to-Many Relationship between People and Establishments.

To make it easier for you to understand I have expanded the Many-to-Many Relationship into two different things, which are called One-to-Many Relationships.

[Dimple] : So Toby, is that like saying that One Person can make Many Visits to many Establishments ?

[Toby] : Yes, Dimple - that's great - and we can also say that One Establishments can have Visits from many People.

At this point, we can show how all these boxes are related, and that is a very big step, because it takes us to the idea of 'Relationships'.

And now we can see how useful our Identifiers can be because we can include the Person and Establishment Identifiers in our Visits table. Then the Person_ID field becomes a link to a record for a Person in the Person Table.
This link is what is called a Foreign Key and we can see it's shown with 'FK' on the left-hand side.
People Establishments and Visits

[Dimple] : Toby, when we go into a shop we want to buy something.
And there are thousands and thousands of possibilities.
How do we deal with all that in our little Data Model ?

[Toby] : Well Dimple, it's really quite easy. It's like all our Modelling where we look for simple patterns that cover many situations.

[Dimple] : Hmm - I don't know what that means. Maybe if you showed me I might understand it.

[Toby] : OK.
Everything that we buy is called a Product, and all we have to do is simply define the type of each Product - such as a Coffee, Muffin or a Newspaper.
Then we draw a little box called Products and say that every Product has a Type.
In other words, there is a Relationship between the Products and Product_Types boxes.
The lines are called Relationships and they are very important in Data Modelling.
We are now creating an Entity-Relationship Diagram or "ERD".
Products and Types

[Dimple] : Toby, when we look closely at the Menu Board to try to decide what to order we can see lots of possibilities

But after a while we can see a pattern that helps us decide.
How do we deal with all that in our little Data Model ?

[Toby] : Well Dimple, it's really quite easy.
We define something called a Hierarchy.
Hierarchies are very common and simply mean any situation where there are Parents, Children, Gand-children and so on.
If we look at the Starbucks Menu Board on the right-hand side we can see a simple example of 'Expresso' and under it a number of different drinks.
My favourite is Caramel Macchiatto.
So in this case, the top-level of our hierarchy is a Product Category called Expresso, and the next level down is a Product called Caramel Macchiatto.

[Dimple] : OK. That sounds OK.

[Toby] : Finally, we show this Herarchy by a dotted line that in the top-right hand corner in the Entity called 'Ref_Types_of_Products'.
This is formally called a 'Reflexive' relationship and is informally called 'Rabbits-Ears'
Products, Types and Hierachies

[Dimple] : Toby, that looks OK.
I guess we can deal with Types of People the same way, can we ?

[Toby] : Yes, Dimple, and Types of Establishments as well.

[Dimple] : OK, that sounds sensible. And do they use these Identifiers in a Database ?

[Toby] : Yes, and what is even better is that the Database will automatically generate a new unique Identifier for you and your Visits and Purchases in case you want to get a refund later.
Types of People and Establishments

[Dimple] : Toby, that looks OK.
I guess we can deal with Types of Establishments the same way, can we ?

[Toby] : Yes, Dimple.

[Dimple] : OK, that sounds sensible. And do they use these Identifiers in a Database ?

[Toby] : Yes, and what is even better is that the Database will automatically generate a new unique Identifier for you and your Visits and Purchases in case we want to keep track of things, like maybe you want to get a refund later so we need to get your details from the Database.

[Toby] : Before we move on, let's talk about Establishments.
One special thing about Windsor is that it has a Castle where the Queen lives and a very large Royal Park, where she keeps Deer.
But when we think about these things, we find that we can simply fit into our definition of Establishments.
Types of People and Establishments

[Dimple] : Toby, with so many People, Establishments and Purchases how do they keep track of everything ?

[Toby] : Well, Dimple, by this time, everything has its own Identifier that is used wherever they need to keep track.

[Dimple] : OK, that sounds sensible. And do they use these Identifiers in a Database ?

[Toby] : Yes, and what is even better is that the Database will automatically generate a new unique Identifier for you and your Visits and Purchases in case you want to get a refund later.
Visits and Purchases

[Toby] : Dimple, let's take a closer look at the different types of people we can find in Windsor.

[Dimple] : OK, Toby. I hope I don't have to think too much because I might get a headache ?

[Toby] : No, Dimple, I will do the thinking and talking and all you have to do is nod your head when you understand.

[Dimple] : OK, Toby. I promise to do that.

[Toby] : We already said that we have Local People and Tourists.
Some of these Local People are shoppers and some of them will be working in the shops.
We will call the workers 'Staff' and we know more about them than we do about the Visitors.
For example, we will probably know the Gender of everybody just by looking at them.
For Staff, we will usually also know their Data of Birth and their Home Address.
Therefore, although they are all People, we know more about Staff than we do about Visitors.

In Data Modelling we have a very powerful approach that we call Inheritance that we can use here.

If we want to describe this in English, we would say that Staff inherit the People_Type_Code and Gender from the parent Entity of People, and in addition, they have a Date of Birth and Home Address.

Does that make sense, Dimple ?

[Dimple] : I think so, Toby.
Is it like saying that we inherit having two arms and two legs from our Parents because they have two arms and two legs, but that we have also have things that are just us ?

[Toby] : Yes, Dimple - that's great - let's take a break and do some shopping !!!

[Dimple] : I like the sound of that, Toby. Can I have an icecream ?
Types of People and Establishments

[Dimple] : Toby, how do we specify that Staff must work in some Establishment ?

[Toby] : Dimple, that's a very good question.
Fortunately, the answer is very easy.
We add a One-to-Many Relationship between the Staff and Establishment Entities
In English, we would say that every member of Staff must work in one Establishemnt and every Establishment can employ many members of Staff.
In the diagram, we show this with a Foreign Key by the establishment_id field in the Staff Entity.
So if we look closely at the Staff Entity, we will see 'FK' by the establishment_id field.

[Dimple] : OK, that sounds good, and I can see how the Identifiers are very important.

[Toby] : I am glad to hear it, Dimple.
There is one more thing I have to say.
We are learning Data Modelling and one important thing about Data Modelling is that it has to follow a set of Rules.
These Rules help us to produce good Data Models and so they they are very important.
One of the Rules is that we cannot include any bits of data that can be derived from any other bits of data.
For example, we usually want to know how many people work in a Shop or Cafe.
Therefore we include a Staff Count field with the Establishment.
But when to comes to finding the value that goes in here, we will count the records in the Staff Table for each Establishment.
Therefore, it's a derived Field and we call it a name that starts with 'derived_' to make things clear.

Does that sound sensible, Dimple ?

[Dimple] : I suppose so, Toby.
But I've got a headache, can we go for an icecream now ?
Staff and Establishments

[Toby] : Dimple, you can see that I am using a Gender Table and People Types Table.
I have given them both names that begin with 'Ref_' to make it clear that they are Reference Data.
This means that the values don't change much and I can use them to define what the valid values can be.
This is a technique that professional Data Modellers but we don't need to worry about it today.

[Dimple] : I'm glad to hear it, Toby !!!
Although it isn't difficult to understand and it seems like a good idea.
[Toby] : In our small example, we have only four kinds altogether - Gender, Types of Establishment, People and Products.
Visits and Purchases

[Toby] : Dimple, if we bring together everything we have talked about, we will see that we have quite a good Data Model that any professional would be proud of.

[Dimple] : OK, Toby. Do you think I will understand it ?

[Toby] : Let me help you by making a list of the Business Rules for our Model :-
  • People can be either Staff or Visitors.
  • People can make Visits to Establishments
  • Products can be Coffee, Souvenirs and so on.
  • Staff have a Date of Birth and a Home Address.
  • People can make Visits to Establishments.
  • During a Visit they can purchase Products.

    [Toby] : OK, Dimple - we have a very nice Data Model and now we can take the break I promised you.

    [Dimple] : That's great, Toby - can I have an icecream ?

    Sure, but before we do I should say something about PF, which appears in the Staff Table.

    It's unusual and it's called PF because it means a field which is a Primary Key in the Staff Table and a Foreign Key to the People Table.

    [Dimple] : Hmmm, I've got a headache, Toby - can we please go an get an icecream ?

    [Toby] : OK, Dimple. You've been a very good girl and you deserve a break.
  • Bringing it all Together
    15) ICECREAM :

    [Toby] : Dimple, I 've got some wonderful news for you.

    [Dimple] : I'm glad to hear it, Toby - what is it ?

    [Toby] : I have found your favourite icecream here in Windsor, which is Baskin-Robbins ;)

    [Dimple] : Toby, are you teasing me ?

    [Toby] : No, Dimple - look, there it is across the road from Windsor Castle !!

    [Dimple] : Wow - that's great, so I can have my favourite Butter Pecan icecream.
    Baskin-Robbins Icecream Parlour

  • I hope you have found this Tutorial fun and useful.
  • I would be very pleased if you could email me with your questions or suggestions. Barry's Photo
      In this Tutorial we have covered the following Topics :-
      1. Hierarchies
      2. Identifiers
      3. Inheritance
      4. One-to-Many Relationships
      5. Many-to-Many Relationships
      6. Primary Keys and Foreign Keys
      7. Reference Data
    Barry Williams
    Principal Consultant
    Database Answers Ltd.
    London, England

  • Home Ask a Question Best Practice Careers Clients Data Models myDBA Search Site Map Contact Us

    © 2010