DMW logo
tel 01732 833085
Tonbridge · Kent · UK

Data Modelling in Practice

How everyone in your business can contribute to planning the fullest and most advantageous use of your data.

Last updated on 2021-04-06 by David Wallis.


Introduction

When the time comes to review how we collect and use data in our business, that’s the time for some data modelling.

Data modelling should involve everyone who in their work collects or uses data. It should not be regarded as a technical exercise solely for the nerds.


Data Modelling Overview

Data modelling is a process for creating a visual representation of the data to be stored and maintained in a computer system. It establishes what items of data need to be stored, and the relationships between those items.

So that anyone can be involved in this important business process, my view of data modelling is of a three-stage process:

There is a section below on each of these stages.


Conceptual Data Modelling

For a start, you need a picture, a sketch, of the data you need in order to satisfy the goals and objectives for your business. This is your concept of the data.

Running this as a white-board exercise, you gain everyone’s input:

Conceptual data model

Your concept is notable for its simplicity. No attempt need be made to render it into any form of system. But it must not lack in completeness.

My advice is to circulate the picture and let people consider it for a while before reconvening for a review. Fail to include something in the concept and there may be implications for your business in the future.

Take, for example, contact data. In all the databases with contact recording that I’ve been commissioned to upgrade there was no provision for a contact changing their name or address, or both, or having more than one address. Possibly not an issue if all that matters to you is current name and address. But what if your database needs to track name and address history?


Logical data modelling

Activity in logical data modelling is directed at refining the data identified in the conceptual data model:

Certain of your people will need to contribute to the logical data modelling process, if provision for essential data is not to be overlooked.

Project logical data model

Also, you’ll need the contribution of an experienced data consultant to provision your model correctly with entities and attributes.

The objective of this shared activity is a schema that you’re confident in as a representation your data that can be passed as the basis of a specification to your chosen developer.


Physical Data Modelling

On completion of stages one and two of the modelling, your data consultant should have a clear and detailed understanding of the system to be created.

The consultant will identify the most appropriate technology to use: e.g. relational database, spreadsheet, XML document. With that in mind the consultant will prepare the physical data model specific to that software.

The model can be contracted out to an appropriate systems developer. (In my work, I’m usually both consultant and developer.)

Taking Microsoft Access (MSA) as an example of the software chosen for a relational database solution, the database developer applies the logical data model to the design of a relational database.

This image illustrates some MSA tables as they might appear in the developer’s physical data model of your MSA database:

Project physical data model

The names of entities and their attributes are those adopted by the developer. Regard this structure as existing under the bonnet of the database.

Ordinarily, users of the database will be shielded from this data structure by an appropriate user interface. The implementation of the interface is a further step in the creation of the database, following on from the data modelling exercise.


Size Doesn’t Matter

It doesn’t matter the size of business, in practice it’s my opinion that data modelling is an essential.

For the SME:

Debbie W phoned me to find out about a database. She explained she was not good with computers and would rather spend her time on doing the business than on struggling to improve her spreadsheets.

Supposing that a database was what she needed, Debbie’s call was to find out how she should use Microsoft Access. She was mightily relieved she did not need to worry about that bit: she could leave that to me.

To plan the ideal database, we needed to get down on paper how Debbie’s business works, how she envisages it developing, and what data would need to be gathered, maintained, and exploited to gain the greatest business advantage.

Debbie is fulfilling the first step in the data modelling process. With the fruits of step one, together we’ll be able to move to step two. From there I’ll have a complete, detailed specification for a database.

For the multi-million-pound manufacturing company:

The company had out-grown its family-run roots. The business’s data management brains was due for retirement. His spreadsheets, upon which the business depended, had no one else who understood them or who could work them.

That’s when I was engaged, notionally to take over the spreadsheets. Printouts of these stacked several centimetres high. I was confident the time was a good time to introduce a relational database.

Stage one of the data modelling proved to be the most involving exercise I’ve been engaged in with a client. I was shown round the factory. I met with the designers, engineers and those in the stores and on the assembly lines. The marketing and sales teams explained to me their ways with customers.

Thank goodness we followed the data modelling process. Had we not followed through, I hate to imagine the mess the database would have been.

What if I’d not learnt at the conceptual stage that Sales and Engineering did not use the same codes for products; that Sales sold specifications that could not be assembled; that there was no agreed structures for costing and pricing; that Engineering spent a great part of its time re-engineering what Sales had sold so that Manufacturing could assemble a product?

And that of the 16, bought-in types of subassemblies in Stock, only the same four types were used in main assemblies? The other 12 types on the shelves, some of them there for years as far as anyone could remember, never to be used.

I appeal to your sense of wisdom to sternly resist any temptation to skip the data modelling process. If you bet you’ll be saving time and expense by skipping, I’ll bet you that you won’t!

“The goal is to turn data into information and
information into insight.”

Carly Fiorina