I made this entry in response to a blog post I read about some anomalies in B2B (Business to Business) structures at Echoes in the Database.
That post essentially referred to what seemed like duplicate records in a table relating to an individual within a client company and was trying to decided whether or not they were truly duplicates or represented two entirely different things that simply looked similar.
It is always difficult when looking at raw data like this to decide exactly what is happening. It is almost impossible to make an informed decision without the aid of a properly structured data model or Entity Relationship Diagram (ERD).
The post goes on to refer to the roles that people can play in organizations and suggests that these structures should be modeled as ‘parent child’ structure. Well some times they can and sometimes they can’t. The following diagram shows two different scenarios.
The two scenarios are similar but essentially different.
In Scenario 1 we simply need to know the name of the person filling the Customer Contact Role and the contact details for the role. In this instance the name of the person filling the role could change yet the contact details could remain the same. The structure is hierarchical or parent/child and can be modeled by a simple ‘one-to-many’ relationship to Customer.
In Scenario 2 the main relationship is with whatever employee has been assigned to the role. If the employee changes so too do the contact details. This is a network structure and needs to be modeld by an intersection entity between Customer Employee and Customer. With this structure an employee could be assigned to many Customer Contact Roles