diaxon.com  

Home Up

 

 

 

 

 

 

 

 

Extract from 2nd edition of "A Practical Guide to Relational Database Design"

Chapter 9: People and Organisations

This section has been included to give some guidance on the modelling of relationships and structures associated with human activity – in particular  "People and Organisations". As just about every organisation has some form of customer or personnel system, it is perhaps surprising that this is one of the most problematic areas of modelling and database design. To do it justice, we would need to write a separate book (maybe we will), however, to keep the subject manageable we will concentrate on one area in particular – that of the concept of ‘Customers’ which offers a rich blend of issues to wrestle with.

Why is ‘Customer’ so vexing?

It is only comparatively recently that the concept of ‘Customer’ has revealed itself as a particularly vexing issue. Why is this? One explanation is as follows. In the past few years we have seen a trend toward a customer-centric view of conducting business and systems based upon ‘Customer’, or CRM (Customer Relationship Management) are much in vogue. If you want first class customer care you need to know all that is reasonably possible to know about the preferences, requirements and propensities of your customers and the history of their relationship with your company. In a competitive market, paying closer attention to customers’ needs and requirements can yield extra business and extra profits - or at least help you to survive by minimising loss of customers to competitors. In order to achieve this, however, information about the customer, their past buying record, their habits, likes and dislikes, changes in fashion, etc. have to be carefully managed and integrated with other corporate systems.

The problem is that many database systems traditionally been designed to accommodate the transaction side of doing business – typically, order processing, stock control, dispatch, posting to ledgers, etc. and not much effort was put into focussing on customer related data structures. However, now that the era of CRM has arrived many companies are refocusing their systems on customers. However, the transaction systems hold huge amounts of related information which need to be reorganised, this often involves a certain amount of database re-design and huge amounts of data cleanup. At worst this can require wholesale merging and de-duplicating of addresses as customer and account data are often held in different and dissimilar systems. You may notice some similarities of approach here with creating data warehouses – the two topics are not unrelated.

What is a Customer?

This may sound an obvious and trivial question but one which is often neglected at the outset. Typically, a customer is a person or an organisation. OK so far so good, person is easy enough to understand? But what, in customer terms, is an organisation? It can sometimes be an organisation name: Microsoft Corporation, a part of an organisation: Microsoft Human Resources, a particular location: Microsoft Human Resources (Head Office), Seattle. The customer might be a particular person at that organisation or a sub-department. There are many different types of organisation – corporations, partnerships, government bodies, institutions, societies, franchises, etc.

So organisations can be a bit heavy? Lets go back to an individual person. A customer might be a bona fide person "James Smith" or he might actually be a registered business or partnership. The annoying thing about people is that it is difficult to find a typical, stable set of details.

Another aspect to bear in mind is the difference in perception of ‘Customer’ in different sections of a business. For example, Accounts, Finance, Sales, Marketing and Distribution departments will all have their own flavours and their own attributes. The Accounts Department is usually concerned about sending invoices and statements, receiving and processing money. The Sales Department's primary role is to secure a sales and is very person/contact oriented as sales are made via real people. Marketing will be interested in spending power, buying patterns, interests, etc. When designing a database, the analyst must be clear what is required and where the boundaries lie and may be how to accommodate the needs of maybe more than one department. Having established that the next thing to consider is what kind of information we need to hold. Here are some commonly occurring attributes and dilemmas to ponder:

About Individuals

Names:

We need to accommodate names for different cultural and so First Name and Second Name are preferable to ‘Christian Name’ and ‘Surname’

Note that names can change over time (by marriage, deed poll, assumed name, pseudonym, etc.) and so it is unwise to use names as part of a primary key.

Address:

The address at which they live can change over time. They may even have more than one address – which may be use for different purposes (home, day time delivery, second home, temporary address, etc.)

Contact details:

May sometimes change: phone number, fax, email, a customer may have multiple and/or preferred contact methods.

Affiliation:

They may work for, or represent, more than one organisation, they may change employment over time. It may be significant if they belong to clubs or societies (Eg. preferential for discounts).

Role

In what capacity is an individual acting? A private person, a representative for others, in a professional capacity?

About Addresses

First Line Street Number, Name, House / Flat number/name and their relative positions vary.

How do you accommodate an organisation name?

Second Line Sub-address, Hamlet, Area
Abbreviations Need to accommodate and identify synonyms (Avenue, Av, Ave. Road, Rd., etc.)
Postal Town or City Will this always be in the same position?
Post code or Zip code May be before or after town or state depending on the country. Should you hold the positional information?
Country Different forms – Eg. UK, GB, Abbreviations, etc. Consider using a country code rathere than a name to ensure standardisation.
   

About Organisations

Names May have permitted variations / abbreviations in the name. May have standard suffix: Ltd., Inc. Pty, SA, Corp., etc. May have trading names.

The name used may not be its ‘legal entity’

Organisation Type/classification Limited Company, Incorporated, Educational or other Institution, Club, Society, Legal Entity, Government-related

Each has its own constitutional rules & governance. The ‘trading’ relationship between these types may vary with your own organisation. Are there business rules associated with the Org type?

Structure/Hierarchy Are there different Divisions, Departments, Sub-Units, etc. to take account of (E.g a university) or are they catered for in the address? You may want to aggregate sales by the highest organisational unit so understanding structure is important. What about looser affiliations: Franchises, Groups of companies, etc.
Contact : How do you deal with contacts? Use a named person or just a ‘role’ (Eg. Manager, Buyer, Head of Dept.). The contact name may vary with time. Contacts may have different roles which may be important. Trading / ordering may only be permitted through authorised contacts.
Structure/Address There may be multiple addresses: regional HQs or outlets. May be different (autonomous) departments and hence different parts of the organisation. (E.g a university). May be that goods go to one address and the invoices go somewhere else.
Trading Relationships The type of organisation may determine your trading relationship. You may have wholesale / retail / discounting / volume agreements which need to be included.

The above details are only a few of the components that need to be considered. At the entity / attribute level, the contents and format each need to be thought out very carefully and the functionality that you wish to satisfy must be at the forefront of the analysis and design process. These are just the fundamental elements that we would need to consider in even the most simple CRM application. Not yet considered, are the associated elements such as the goods ordered / invoiced, the interests that the ‘Customer’ may have in what your company can provide or the relationships between these elements.

It is evident that, for people and organisation-related information, there is much variation between types and there is inconstancy because of their changing relationships and attribute content, over time. This poses certain dilemmas because on the one hand, there is a need for flexibility to accommodate such a variable information but on the other, the architecture must be structured and formal so as to provide a firm base for the functionality. This is why, as stated at the beginning, this area of data modelling can be problematic.

The Data Cloud Concept

Earlier, we discussed the use of a ‘Customer reference’ as being a suitable PK for ‘Customer’, the reason put forward for this was because many customer-related attributes, are liable to change over time. In fact, we can consider the Cust Ref. as about the only stable attribute residing amongst a cloud of changing information. Another point to note that is that a high proportion of customers are not ‘Islands’, they share attributes with each other and therefore, form complex relationships between them. Consider the following attribute inter-relationships (business rules):

  • A customer may be a person and/or an organisation.

  • An individual or organisation may reside at one or many addresses.

  • An address may be occupied by one or more individuals and/or organisations.

  • An organisation may or may not be represented (contact) by one or more individuals

  • An organisation may be multi-layered with many branches and affiliations.

  • An individual may hold one or more roles within an organisation (Student, Manager, Buyer, etc.)

  • An individual may be employed or affiliated to one or more organisations (consultant, visiting professor, director, etc.)

  • An individual may be related (literally) with other individuals (Eg. discounts, etc)

  • An organisation or individual may provide goods or services only in certain sectors.

  • The same type of goods may be bought by different organisations and individuals.

The better the understanding of these relationships, the better equipped a company is able to sell its goods or services. Better (accurate, more complete) information leads, through analysis, to greater understanding of the market – who to concentrate on, how often and where. This promotes efficient use of resources and better customer care.

So how do set about building a Customer-centric model? Firstly and fundamentally, we have to understand how our people-related information is structured and how and what components may change over time. The adjacent model acknowledges the business rules provided above:

In this first cut of the model we recognise the many:many relationships between ADDRESSES, INDIVIDUALS AND ORGANISATIONS. However, it is apparent that this model is naive as it cannot take into account the roles that individuals play within orgs or the roles that are played within the addresses for orgs or individuals.

How do we represent the centre of our cloud? Where does the Cust Ref attribute and PK come in? As a customer’s characteristics vary with time and tie in the various characteristics such as address, there must be some kind of intersection record sitting in the middle.

We can take the model a step further to resolve the many : many relationships required and we see how the Customer Ref concept becomes a central hub amongst the surrounding entities.

We have also improved organisation modelling so as to allow hierarchical structuring. The type of association is typed as is the ORGANISATION. In reality there may be various ways of classifying an organisation so either multiple type entities may be required (Eg. Academic, Hospital or Electronics, Consumer). Alternatively an intersection can be introduced to allow an indefinite number of type classifications. The nature of roles is also worthy of note. You may be dealing with individuals (Individual role-type) acting on their own behalf or with someone acting on behalf of the organisation. In the model shown, we could use ROLE TYPE to identify and occupation or status (Business Analyst, Student, Company Director, General Practitioner, Unemployed). Alternatively the role could reflect the position within a company (Organisation role-type) such as Head of Department, Chief Buyer. In this latter case, it must be made clear whether the individual (if attached to the CUSTOMER REFERENCE) is of primary importance or the role itself – the individual being only a present incumbent. The model shown is not really explicit enough; the predominance of the Organisational role type could be implemented by business rules, maybe using an extra flag attribute (Eg. when flag set then role is predominent else its an individual role). Alternatively, the model could be made more explicit, maybe by introducing a sub-type into CUST REF ROLE.

Note, however, that the above diagram is a logical view of how information is really structured. There is no de-normalisation to be seen here. If this model is to implemented in part of a database then we would expect addresses and organisations to be shared by different customers. Functionally, this could be quite difficult to achieve if say, we are to populate the database from legacy systems – which hold multiple addresses and organisations (merge and de-duplicating required). To support this structure, rigorous validation will be required when a new customer is entered – (does the address / organisation already exist, if it does connect to it, if it does not, create a new entry). Similarly, if a customer moves, should the old address be deleted, or does someone else still live there? Greater rigour is required – but it will pay off in the long run because of the high level of data quality. It all depends upon the exact functional requirements.

For the sharp-eyed, you may notice that the structured modelled here for Customer looks remarkably like a ‘star’ / ‘snowflake’ structure as described in the section on data warehousing. There is a very ‘dimensional’ approach to modelling here.

Customer Reference vs. Customer Accounts

PRODUCT BUYING RECORD on the model is shown here as an abbreviation for a relationship to orders / invoices, etc. pertaining to the customer. Note that once again it is the customer reference which makes the connection. Many organisations use the concept of customer account records and often an entity named ACCOUNT (or customer account), is synonymous with CUSTOMER REFERENCE as used here. The Accounts and Finance departments, in particular, work with accounts. However, the two entities are not really synonymous (unless used on a one : one basis), very often a customer may have several accounts (take banking for example), more rarely, several customers may share an account (E.g. a family). The account mechanism makes for an easier life for the Finance/Accounts departments as they can regard them with detachment, most of the time, providing the accounts are paid up, the Customer concept is of secondary concern.

A good example of where Accounts and Customer Refs. differ is in the publishing industry. An academic journal (and book) publisher may keep track of the interests of academics and professionals and occasionally target them with marketing campaigns, say, for new journals. Exceptionally, the academic may purchase a subscription directly from the publisher. However, more often than not he/she will request the college or Department’s librarian to subscribe. Hence the employer organisation may make the subscription. Alternatively, the librarian may buy the subscription through a Library supplier or book agent. The poor old publisher, therefore, has to monitor and track his potential readers who will probably never subscribe directly and their organisation or institution may buy through a Third Party. This is a headache for the Sales & Marketing department who have difficulty in matching potential customers (readers) with actual customers (Account holders). The final irony for this kind of organisation is that the readers (academics) are also likely to be contributors (authors) to the journals. This is the stuff that Data Modellers dreams are made of!

So, the model suggested is idealised and very general. It has been put together without any specific real functional requirements. However, if translated into a database design it is a practicable model. In the real world, the structure of the data model depends on the precise requirements. Modelling in this area is by no means easy and one which the authors, despite considerable experience, find extremely challenging.

CRM Products

Of course the market is crowded with ‘solutions’. Some specialised products do go a long way to encompass all the likely scenarios for customer-related structures, these tend to be large and ungainly and require considerable implementation and support. Others, compromise on sophistication for the sake of simplicity and mainatainability (and price). You pay your money and take your choice. If you are thinking of buying a package – but on a limited budget, you might have to compromise your business functionality!

Conclusion

As intimated at the beginning of this section, People and Organisations (especially customers) is a potential minefield for the inexperienced data modeller. You must understand the requirements exactly as small details (as in the ‘devil in the detail’) are of the greatest significance in this, the most difficult of business areas. It is comparatively easy to build an all embracing logical model (we’ve all done it at one time or another) BUT going physical with it can shatter all your illusions of success in the subject. Busy departments, perhaps chasing difficult sales targets, understaffing, etc. can mean that maintaining customer details to the degree and quality that are required, say for analysis in a data warehouse, is not practicable. It may be better to build a more robust, simplified model and rely on cleansing processes ‘downstream’ than to attempt to build the perfect system. Look at your business processes, the organisational environment, the staff skill levels and other operational factors as well as the pure information model before completing your designs.

© Diaxon Ltd. 2000-2009 Last updated: 21/11/2009