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.
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.
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:
For a multi-million-pound manufacturing company:
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!