Techniques in reviewing a data model

There are a number of techniques I apply when I review a data model. Using these
techniques over the years helped me quickly summarize models and look for ways
to improve them. Many times the 80/20 rule seems to prevail when reviewing a
model. That is, in 20% of the time we can validate 80% of the model. (That extra
20% can be a bit tough however!)

Many of these techniques I plan on one day publishing and making it a topic in
my modeling course. An example of such as technique is:

I look at each data element and quickly identify which data elements do not end
in a valid classword. A classword represents a general domain for the data
element and examples include AMOUNT, DATE, NAME, IDENTIFIER, INDICATOR, and CODE
(there are others too). Finding data elements that don’t end in a valid
classword represent a naming standard violation at a minimum, and can also
signal a larger problem where multiple pieces of business information might be
stored in the same data element. For example, is Gross Sales an amount, a
weight, a value, or worse yet, a combination of these? That is, should the right
data element name be Gross Sales Amount, Gross Sales Weight, etc.?

Now I know there are a lot more tips out there that you might use when you
review a model. Share these with me. I will compile some of these techniques
(including more of my own) and send them back out in this design challenge
response. Note that as with all design challenges if I use your technique (and
it’s not already on my list) I will give you credit in any documentation.
Looking forward to hearing your techniques!

Response

So what are some of the handy techniques we use when reviewing a data model? The
more models we review, the more techniques we tend to apply either formally or
instinctively. This design challenge contains several of the very useful
techniques that were sent to me for reviewing a design, thank you for the great
responses! A special thanks goes out to data modeling legend Graeme Simsion for
his responses.

I placed each of these techniques within categories and there is no priority to
the order I listed the categories in or the techniques within each category.
They are all important techniques, and this design challenge focused on
identifying the techniques rather than rating them. As you read each of the
techniques, do you see any new ones you can start using?

(The name in parenthesis following each technique is the person who submitted
the technique.)

[NOTE: Whenever you see notes like this within the square brackets, they are
additional thoughts I have on the technique.]

Validating the model with the business and functional
experts

• Express relationships and subtype hierarchies as business assertions eg “Each
  Person may hold one or more accounts and each account must be held by one
  person.” Richard Barker’s technique was the first I saw; Graham Witt’s
  which incorporates entity and attribute definitions to provide a complete
  description of the model is the best. The usercan workalone on this
  (initially at least!) ticking off – or querying – assertions serially. (Graeme
  Simsion) [NOTE: This is a very powerful technique, especially when the
  business or functional experts prefer to stay clear of the boxes and lines on a
  data model. We are simply reading them the story on the design. When you do
  this, I strongly recommend reading them what the relationship includes, as well
  as what the relationship excludes. For example, in Graeme’s example, if a Person
  can hold one or more accounts, that would mean a Person can not hold zero
  accounts. I would ask the business experts whether this is what was intended,
  and if so, we have it modeled correctly.]
• Start to gently query the author about some of the non-obvious business rules
  during a model-walkthrough session. (Ashraf Kandil)
• Perform transactional analysis. Ensure that each of the target transactions or
  desired reports can be produced based on the suggested model. (Ashraf Kandil)
  [This is a very useful technique when designing a data mart where validation can
  be done by reviewing a report and making sure everything on the report can be
  represented somewhere in the model. A word of advice here: there is a very good
  chance your design might require more flexibility than what appears in a given
  report. For example, if there are four levels of product reporting within a
  certain report, build your design with enough flexibility to handle the maximum
  number of levels your experts feel might exist in the near future.]

Data element and entity names

• Check entity names for non-singular and group names – eg Customer List, Product
  Catalogue. For consistency (and in building assertions) entity names should
  describe one instance. (Graeme Simsion)
• I look at each data element and quickly identify which data elements do not end
  in a valid classword. A classword represents a general domain for the data
  element and examples include AMOUNT, DATE, NAME, IDENTIFIER, INDICATOR, and CODE
  (there are others too). Finding data elements that don’t end in a valid
  classword represent a naming standard violation at a minimum, and can also
  signal a larger problem where multiple pieces of business information might be
  stored in the same data element. For example, is Gross Sales an amount, a
  weight, a value, or worse yet, a combination of these? That is, should the right
  data element name be Gross Sales Amount, Gross Sales Weight, etc.? (Me)
• Ensure that if an entity/table/attribute/column naming standard already exists
  in the organization that it has been adhered to. Otherwise improvise with one
  and apply it consistently across the model. (Ashraf Kandil) [NOTE: As modeling
  tools continue to add features, naming standards validation should eventually be
  an automatic and not a tedious manual activity.]

Model appearance

• If you’re presenting a diagram toa non-specialist, start with a “high level”
  diagram stripped ofthe “less important” entities. Use your judgement
  here: the purpose is to get theperson oriented; the actual verificationwill
  take place against the full model. Another optionis to draw the important
  entities larger – if your tool will let you. (Graeme Simsion) [NOTE: I build
  “high level” models often for the reason Graeme mentions. It’s similar to the
  concept of reading a table of contents to a book to get the high level
  understanding of that book as opposed to jumping right into page 1 of the text.]
• A favorite technique of mine when review with the business, is to divide the
  logical model into business subject areas. This is easily done with
  ERwin’s subject areas. Most often, the subject areas correspond
  todiscrete business processes, e.g.,add a customer, send an invoice, etc.
  I walk the business through each subject area – one at a time
  -describingentities,dataandrelationships in the context of the business
  process and business rules.(Mary Niva)
• Check model clarity suggesting the use of subject areas if overly complex. Try
  for a one page fit for each subject area. Investigate the potential of using
  colors to highlight important entities eg, yellow for transaction entities and
  plain white for valid-value set (lookup list) entities. (Ashraf Kandil)

Rules of normalization

• Verify each entity is in third normal form. Ask intelligent questions about the
  ones that aren’t and evaluate the answers. (Ashraf Kandil)
• Look for 3 (or more)-way intersection entities. Check the rules to ensure
  4th and 5th Normal form have not been violated (not so hard when you think
  “rules” rather than “multi-valued dependencies!”) (Graeme Simsion)

Definitions

• Look at a selection of definitions – poor definitions are one of the most common
  faults in a model, and often overlooked as the focus is on the graphical
  representation. A quick glance will usually tell you whether the modeler
  has taken this task seriously. (Graeme Simsion)
• Ensure that entities and attributes have good solid meaningful definitions that
  are more than just a repetition of the name. Encourage the provision of examples
  to enhance understanding. (Ashraf Kandil) [NOTE: How many of us have seen
  definitions that simply rephrase the data element name? These “quick and dirty”
  definitions can escape notice when searching for missing definitions.]

Model flexibility

• Look for “head scratchers” (self-referencing one-to-many and many-to-many
  relationships). Is the flexibility of an infinite hierarchy / network
  really required or has the modeler just been too lazy to model the real
  structures? (Graeme Simsion)
• Look at how “parties” have been handled. Many, many modelers will
  generalise “all people and organizations of interest to the enterprise” into
  some sort of Party, Party role etc structure without checking whether the
  business needs that level of generalization eg the business may be quite happy
  to treat suppliers and shareholders as totally disjoint (even if in the real
  world they overlap). (Graeme Simsion)
• Listen to the business case/problem and verify that the model is addressing it
  and not an academic exercise DON’T MODEL FOR THE SALE OF MODELING (Ashraf
  Kandil) [NOTE: There has to be good business reasons why we chose a particular
  design structure.]

Keys

• Look for meaningful keys (ie non-surrogate keys). My view is that these are
  acceptable sometimes, but we should always check that they are applicable for
  all cases, unique, and stable (the last of these three is the most common
  problem). (Graeme Simsion) [NOTE: If you do decide to create a surrogate key,
  make sure you define an alternate key on something real such as what the
  business would view as the primary key.]

Specific to the physical
data model

• Check for Recursion. Look for any recursive relationships. Verify that they have
  been correctly resolved. Ensure that the resolution comes with a Type entity.
  (Ashraf Kandil)
• Suggest a Super/Sub type resolution approach. (Ashraf Kandil)
• Identify if the nature of the target system and verify if OLTP or DW. Check for
  any controlled redundancies for the latter. (Ashraf Kandil)
• Verify if any logical model entities should be combined into physical tables to
  achieve performance expectations. Verify correct dimensional Hierarchies have
  been established taking into account business nature and physical modeling
  science. (Ashraf Kandil) [NOTE: Remember to apply the Denormalization Survival
  Guide here from Chapter 8 in my book – a shameless plug for my book!]
• Perform throughput analysis. Identify that, where possible, high volume
  transactions have been well catered for to achieve performance expectations.
  (Ashraf Kandil)

Meta data quality

Does the meta data match the data? If at all possible, try to get at the actual
data that will be stored in the data elements you are modeling. You might be
surprised at what you find, but better to be surprised early on in a project
than during the actual development. For example, I recently worked on an
application where an email address was being stored in a company name field. It
can sometimes be challenging to actually get to the data, but once you get
access to the data simple SQL queries can help validate that the data element
meta data matches the actual data. (Me)