Business Systems Modelling: Data Structure Modelling (Tutorial 2) by John Owens

A poorly designed data model can cause major data quality issues when the final information system is launched. 

In the second part of his expert modelling series, John Owens provides another detailed tutorial that explains the core techniques of effective data structure modelling as found in his Integrated Modelling Method (IMM).

If you missed the first tutorial, check out John Owens content here: Business Systems Modelling, Function Modelling (Tutorial 1).

To most business people data modelling is a black art practiced by the company IT department that brings no tangible business benefits, being solely designed to make mere mortals feel confused and inferior.

Sadly, many IT departments foster this view and, for those that do, the data modelling that they A poorly designed data model can cause major data quality issues when the final information system is launched. 

In the second part of his expert modelling series, John Owens provides another detailed tutorial that explains the core techniques of effective data structure modelling as found in his Integrated Modelling Method (IMM).

Poorly designed data models will almost certainly lead to major data quality issues for any systems that are ultimately built on the underlying models.

It does not have to be this way.

Data modelling, when done properly, can deliver enormous business benefits to any enterprise, that include:

  • Higher quality information for all business activities
  • Easier access to that information
  • Robust information systems
  • Better identification of products, profit and cost centres.
  • Elimination of redundant and unneeded information
  • Reduced costs and increased revenues

But how do you do data analysis and modelling “properly”? Where do you start?

Here are two simple rules that will guide you on your way:

Rule 1: Extract all information for data modelling from exactly the same sources from which you extracted your Business Functions

Rule 2: Only model data that is needed to directly support the Business Functions of the enterprise

Starting with Rule 1 will make sure that you conform to Rule 2.

The Integrated Modelling Method provides a foolproof technique for extracting candidate entities, attributes and associations from the sources from which business functions were extracted. This technique can be used by novice and experienced analysts alike.

These sources include:

  • Transcripts of taped analysis interviews with senior business managers
  • Typed up notes of supplementary information from these interviews
  • Function titles and descriptions developed during function modelling
  • Information flow diagrams produced in analysis workshops

Technique

The basic technique:

  • Step 1: Work through data sources (best to have these in electronic format) looking for and underlining all “noun structures”, as these are “candidate” entities.
  • Step 2: Extract all of these candidate entities and the associations between them into a separate document.
  • Step 3: Convert these candidate entities and associations to actual entities, attributes and relationships.
  • Step 4: Build an entity relationship diagram (ERD).
  • Step 5: Design any required relational databases from the ERD.

Example

The first steps in the technique are best demonstrated by way of an example.

The following is a transcript of an interview with a business manager:

“We receive orders for our products from our customers the day before they require delivery.

We check the quantity of the raw materials required to bake the products and, if necessary, we order more from our suppliers.

We bake our products fresh each morning. We make deliveries to our customers several times each day. At the end of each week we invoice each customer for the deliveries made to them during the week. We accept payment or remittance from customers by cash and cheque only”.

All of the noun structures have been highlighted.

The first sentence is:

“We receive orders for our products from our customers the day* before they require delivery.

Working through the sentence one noun at a time we get the following list of candidate entities and associations:

  • order <means of ordering> product
  • product <ordered by means of> order
  • order <received from> customer
  • customer <the source of> order
  • product <delivered by means of> delivery
  • delivery <means of delivering> product
  • customer <recipient of> delivery
  • delivery <made to> customer

Note: day* is an attribute of order

Because every association is two-way, when we document one association we immediately create its reverse.

Working through the whole of the transcript gives us the flowing complete list (sorted alphabetically):

  • baking <to produce> product
  • customer <billed by means of> invoice
  • customer <recipient of> delivery
  • customer <source of> payment
  • customer <the source of> order
  • delivery <made to> customer
  • delivery <means of delivering> product
  • delivery <of products billed on> invoice tbv
  • invoice <a billing for> product
  • invoice <a means of billing> customer
  • invoice <billing for goods delivered by> delivery tbv
  • invoicing period
  • order <means of ordering> product
  • order <means of replenishing> raw material
  • order <placed with> supplier
  • order <received from> customer
  • payment <accepted from> customer
  • payment <made by> payment method
  • payment method <valid means of making> payment
  • product <billed for on> invoice
  • product <delivered by means of> delivery
  • product <ordered by means of> order
  • product <produced by> baking
  • product <requirement for> raw material
  • raw material <quantified by means of> stock check
  • raw material <replenished by means of> order
  • raw material <required to bake> product
  • raw material <sourced from> supplier
  • stock check <to establish quantity of> raw material
  • supplier <recipient of> order
  • supplier <the source of> raw material

This short extract has given us eleven unique candidate entities and thirty (15 x 2) candidate associations.

Rationalising Entities

The list of candidate entities now needs a little more work to remove false of spurious entities. A typical example of a candidate item that is not a proper entity is “invoice”. An invoice is probably the most common business item to be incorrectly modelled as an entity. The invoice itself is a piece of paper that represents a business entity or a collection of entities such as a sale (of one or more products) or a billing (for one or more sales). These are the actual data entities that should be modelled – not the pieces of paper representing them.

Converting Associations to Relationships

The associations that we identified must now also be rationalised and converted to “Relationships”. Associations simply tell us that two entities are associated and give us a suggested name for that association. A relationship tells us all of the essential information that we need to know about the association. This includes

  • The precise name of the relationship
  • Whether it is mandatory or optional
  • Its “degree”, that is, is the relationship is one-to-one, one-to-many or many-to-many

The Entity Relationship Diagram

All of the preceding information is essential to know but is almost impossible to visualise and of limited use without constructing an Entity Relationship Diagram (ERD). This is the single most powerful model for use in the understanding of the data structure of the enterprise and an essential element in the design of quality databases.

The many ends of a relationship on an ERD in the Integrated Modelling Method are indicated by the following symbol , which, for obvious reasons, is called a “crows foot”

If we turn this symbol upside down we get a “dead crow” 

This gives rise to one of the most powerful, yet simple, rules for achieving a really effective layout for any ERD, which is “Dead Crows Fly East“. This means that all “many” ends of relationships in ERDs should appear either like either of the symbols below:

The net result of this is that all high volume and volatile entities will appear to the top and to the left of the ERD and all low volume and more constant entities will appear to the bottom and to the right, as in the following simple ERD.


All of the rules which were briefly described above are expanded on in full detail, together with many more, in the eBook Integrated Modelling Method: Data Structure Modelling, available here IMM™ Data Structure Modeling

Useful Resources


John Owens

John Owens, Business Modelling Expert, IMM

John Owens, Business Modelling Expert, IMM

John Owens is passionate about bringing simplicity, power and elegance to the world of Business Systems Analysis, Business Process Modelling and BPM. 

He is an international consultant and mentor to a wide range of enterprises of all sizes in the UK, Ireland, Europe and New Zealand. He has put all of this knowledge into a set of books and the Integrated Modelling Method (IMM™) which is available at his website www.integrated-modeling-method.com

John is based in New Zealand and provides mentoring to enterprises of all sizes, from start-ups to large corporations, to aid them improve their business and increase their cash flow.


Leave a Reply 0 comments