How To Use MECE for Data Model Design
Practical Applications of a Time-Tested Technique
When designing a new product, how do you ensure that your team is building a lasting solution? Proper data model design allows for operational clarity and helps maintain data integrity. The best systems make sure that objects don’t logically overlap with each other, and that fields existing in one object shouldn’t conceptually exist in another. Even within each field, are the values distinct and encompassing of all possibilities?
What is MECE?
Developed by Barbara Minto at McKinsey & Company in the late 60’s, MECE (commonly pronounced as “mee-see”) stands for Mutually Exclusive and Collectively Exhaustive. It’s a grouping principle for categorizing sets and subsets of items, and it’s ruled by 2 guiding ideas.
Collectively Exhaustive means that the existing groups account for every possible outcome.
Mutually Exclusive means that each group is distinct from each other with no possibility for overlap.
You’ll find many posts online explaining the MECE principle very broadly. For example, a common MECE grouping is “X” vs. “NOT X”. When applied to reality, we can do things like picking a spot for dinner by narrowing choices down to “Fast Food” or “Not Fast Food”. You can then created sub-categories for both groups to help you decide.
I wanted to take MECE a step further, focusing on how it can be applied to data models, categorization, and definitions of data elements.
A simple example of MECE is the six-sided-die. There are six possible outcomes, no more and no less. When you roll the die, the outcome always a single integer between one and six.
Start Talking to the Experts
Like many organizations, Salesforce was essential for handling our clients’ end-to-end life cycle. Unfortunately, the system was riddled with data quality issues which had slowly appeared over a long period of time. My team took the lead role in redesigning a brand new CRM. We functioned as internal consultants to identify business requirements and generate meaningful results.
Before jumping into data model design and data classification, I recommend taking a step back and considering the perspective of the business and the client. We organized a lean team of key operational players who understood the business well and also worked hands-on with the data. It sounds cliche, but we told the team to forget everything they know about the existing Salesforce structure. We considered the needs of the customer, the operations team, and the key business function. Over the following days, we started to see the bigger picture of how the system should function in an ideal state. This transformed it into a novel, logical data model, devoid of any baggage carried over from the old system. Using language like “objects” instead of “tables” allowed us to focus on conceptual design.
Forget what you know about the existing data model and think of why the system exists to fulfill the needs of the client or the business.
How to Apply MECE
It’s important to apply MECE at every level of application, and below are listed examples of how to approach this task. Usually, tables easily conform to MECE whereas their defining fields require more attention. MECE ensures that information in each field doesn’t overlap with other fields (ME), and together, all fields fully describe the table in it’s entirety (CE). Here are some questions you should ask yourself when applying MECE to a data model.
Table to Table MECE
(ME) Is there any overlap between conceptual definitions of tables?
(ME) Are there fields that could logically exist in multiple tables?
(CE) Do all tables collectively describe the entire workflow of the system?
Field to Field MECE
(ME) Could values in one field logically exist in another field?
(CE) Based on the definition of the table, are critical data elements not covered by all the fields?
Is the terminology you use to name each field consistent within this table and across other tables?
Value to Value MECE
(ME) For date types, what granularity of time, day, month, or year is needed?
(ME) For picklist types, could any current or future record be described by multiple values?
(CE) For picklist types, do you need to include an option for “Other”?
(CE) For numeric types, is there a lower or upper limit?
When defining what goes in to each field, MECE is incredibly important for operational ease-of-use and future data integrity. For each major object, we began to define the fields by naming them (making sure to use consistent terminology) and deciding on their contents.
It’s important to note the usage of MECE when defining picklist values. A picklist requires a predetermined set of items, of which only one can be selected (although multi-select is also possible). Picklists work great for fields like type and status because values will remain fairly constant even with changes to the business. Picklists also increase data integrity and decrease operational confusion. See below for examples of practical applications.
It’s important to apply MECE at every level of application: table-to-table, field-to-field, and value-to-value.
Practical Examples of MECE
Client Status
A common set of client statuses can be defined as Active, Cancelled, and Completed. Thanks to MECE, we found a blind spot where clients were new to the system and not yet Active. We created a new bucket called “Onboarding”. With these values, Clients can move between statuses, but never exist in multiple statuses at once.
Sub-Status
(A good example of what not to do)
There are cases where you want your main status to be as simple as possible, (i.e. “Fast Food” vs. “Not Fast Food”). This necessitates a sub-field to provide additional clarity. While possible, notice that adding a sub-field like “Sub-Status” generates additional complexity. The main status is simpler, but the sub status has duplicate information, like the possibility for an Active-Active client, or contradictions like an Active-Cancelled client. This model demands data constraints to ensure that all combinations of Status and Sub-Status make sense, which is additional work for the developers.
Payment Status
Another common application of MECE in data classification is the statuses that all payments go through when being processed. MECE prevents a transaction from getting lost in the system. This status workflow can vary between different processors, but many of these statuses are used consistently across industries. Given the amount of different categories involved, it would be necessary to have a glossary of terminology in addition to the clear language used to name each status.
Don’t be afraid of increasing complexity. The complexity is inherent to the system. Just because the business is complicated, doesn’t mean the data process needs to be hard to understand. You can achieve clarity through design.
