Simple Data Modelling

Everyone has to start somewhere. An expert in any field does their first work sometime. Every data modeller does their first piece of simple data modelling at some time.

Whether you are an expert or someone wanting to start newly with data modelling, here is an example of data modelling in its simplest form. Instead of a piece of paper as I used in the past, we will use a database table with columns.

A cupboard full

Imagine you have a cupboard and it is full of pots. For some reason, known only to yourself, you decide that you want to store information about your pots in a database. You are not interested in knowing the shelf they live on, or which room in your house contains the cupboard; your interest is solely in information about the pots themselves.

All sorts of different names can be given to the data models we might produce – logical models, physical models, conceptual models and so on. For the time being, we will put all of that aside. We are simply trying to store information about a physical object in a database table, and how we do it is up to us.

A table full

We start with giving our table a name. This seems simple enough, but be aware that there has been lots of conflict over whether the name should be plural (because it will contain information about more than one pot) or singular, because each row in the table is a single pot. Either is logical. Either is reasonable. Most people will have a preference, and I am no exception. Let’s call your table POTS (yes, plural!). Oh, and another thing, in giving your table a name you are making a whole raft of extra decisions (standardisation, capitalisation, characters used, etc.), but we can also ignore them for the present. Maybe another time we will look at those.

Each pot in your kitchen cupboard will be represented by a single row in this table. So what do you want to know about your pots? What will distinguish one from another?

This is real data modelling.

Asking Questions

Some questions to help you decide what you really want – or need – to know.

  • Do you want to know what material they are made of?
  • Does it matter to you where you bought them?
  • Is their colour important?
  • When did you buy them? (Do you care?)
  • How much did they cost? (Or can’t you remember for half of them anyway?)

For most people who simply want to use their pots, most of these characteristics are not important. OK, then, what characteristics do we care about?

We need…

Here we use the same method for modelling as we use in ordinary life. When we go to choose a pot from the cupboard, we make a choice based on a few characteristics such as:

1. Type of pot (saucepan, frying pan, wok, egg poacher, etc.)
2. Size (diameter, volume, number of items it will fit, etc.)
3. Is it available (it may be currently in use for cooking or storing something else)
4. Has it already been used (re-use can minimise cleaning work afterwards).

And then we have the final question: Do I like it? We all have preferences and tend to use the same items of equipment all the time.

When storing information in a database, we will store each attribute in a separate column, choosing the column type based on the information to be stored. Numbers will be stored in a column with a number type, text in character fields and so on.

The next post Table columns (#5) will continue our simple data modelling by looking in our cupboard and modelling the things we find there.