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 2023-07-19 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.
Please email me if you need any assistance with your plans for your data.
Data modelling
A business will benefit from engaging anyone who collects or uses its data in the process of determining how IT provisions for those data:
By engaging everyone who collects or uses its data, a business will benefit in precisely determining how IT provisions for those data:
- To picture the business’s workings and the data required to support these workings
- To minimise effort and cost — by disguishing between needs and wishes — in creating a new database, spreadsheet, or whatever, or modifying an existing one
- To establish the rules that relate the different aspects of the data, e.g. between customers and the products or services they purchase
- To identify how analysis and reporting of the data are to present the greatest business advantage.
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:
- Conceptual data modelling
- Logical data modelling
- Physical data modelling.
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:
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:
- Data are arranged as entities, such as Person, Organisation, Address.
- The attributes of each entity are identified. For the person entity, CurrentTitle, CurrentLastName and CurrentFirstName might be three of its attributes.
- Assigned to each attribute are its properties. Principle amongst these will be its data type. For example, a Product entity has a ProductNumber attribute. If your product numbers are always numbers, then that attribute’s data type property is numeric. If your product numbers mix letters and numbers, then its data type property is text, or string.
- Relationships tie entities together.
Certain of your people will need to contribute to the logical data modelling process, if provision for essential data is not to be overlooked.
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 of your data, which can be passed to your chosen developer as the basis of a specification.
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:
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.
Business 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 an 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 most, 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 envisaged 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 a 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, the 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, I hate to imagine the mess a database would have been.
- Sales and Engineering did not use the same codes for products?
- Sales sold certain specifications that could not be assembled?
- There was no agreed structure for costing and pricing?
- Engineering spent a great part of its time re-engineering what Sales had sold so that Manufacturing could assemble a product?
Pursuing data modelling, the company learnt of matters relating to their busines not pertaining directly to the data itself. For example, of the 16, bought-in versions of a sub-assembly held in Stock, only the same four versions were used in main assemblies. The other 12 versions remained on the shelves — some of them there for years as far as anyone could remember, and 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!