A Permission Puzzle

A Permission Puzzle
Based on a challenge submitted by Ben Ettlinger – thanks Ben!We are building a web application that will allow customers to update profile information for the facilities to which we provide power. Here is a subset of the model:


A CUSTOMER can own one or more FACILITIES

A FACILITY is owned by one and only one CUSTOMER

Permission rules:

If a CUSTOMER is not a Government Agency, they can update information only on their FACILITY.

If a CUSTOMER is a Government Agency, they can update information only on their FACILITY and also for other Government Agencies.

How would you modify this model to support these two permission rules?


By the way, our raffle winner for this Design Challenge wasLarry Weismantel. Congratulations Larry! Email me your mailing address and a prize awaits you!

Jamie enjoyed the taste of the paper very much!

We had a lot of responses to this challenge (which is a very good thing!), and to keep the challenge “short and sweet”, I needed to do quite a bit of summarizing and in a few cases I created data models to represent entire paragraphs of text . I apologize in advanced if too much was snipped from responses, but I think the essence has been captured.

I’ll first talk about how I solved the problem and discuss a few of the responses that had similar solutions. Then we’ll see some responses that incorporated additional flexibility, followed by Graeme Simsion’s response on when to show the rules on the model. Then we’ll discuss two responses favoring not showing the rules on the model, including an insightful summary from Dave Hay.

Should the rule even be enforced on a model? Tim Sutin in his response put it well by saying “It is important to realize what needs to be supported with the data – the business rule itself, or the facts based on which the rule can be implemented.” Well on to the responses!

How I solved the challenge

Recall the original model:

To support these two rules on the model:

If a CUSTOMER is not a Government Agency, they can update information only on their FACILITY.

If a CUSTOMER is a Government Agency, they can update information only on their FACILITY and also for other Government Agencies.  

I changed the model to:

I first changed the meaning of the relationship that connects CUSTOMER with FACILITY. Originally a CUSTOMER could only Own FACILITIES and I changed this to also including Maintain. A CUSTOMER can own and maintain their own FACILITY. We still need to include the rule for when a CUSTOMER can update other CUSTOMER’S facilities. This is where subtyping comes in handy, as the subtypes GOVERNMENT AGENCY and GOVERNMENT FACILITY, along with their associative entity GOVERNMENT AGENCY FACILITY PROFILE, contains all of the rules behind which GOVERNMENT AGENCY can update which GOVERNMENT FACILITY.

The following folks had identical or very similar solutions to this:

Doug Domoth, Ruth Sas, and Jose Enrique Gonzalez had almost identical answers to this one.

David Rath submitted some actual logical and physical models on this one that were similar to this solution but touched on two areas important to mention. David showed more than one subtype on his solution. He had both PRIVATE SECTOR CUSTOMER and GOVERNMENT AGENCY CUSTOMER. You do not need to show more than GOVERNMENT AGENCY CUSTOMER to represent the two additional rules, but it is not a bad idea for completeness and for thinking of the future.

Dave also proposed a physical modeling solution where we resolve subtyping by using the “identity” technique. This translates into keeping the supertype and subtypes as separate physical tables so both of these rules can be enforced. There were other physical solutions to this problem too including from Peter Heller  and Ashton Thorogood. Peter Heller proposed an efficient solution for having the code validate these rules. He suggested that when we get to the physical modeling phase, we might consider having a single bit within CUSTOMER, which says ‘1’ or ‘0’, depending on whether the customer is a government agency or not.  Larry Weismantel proposed this same solution at a logical level. Ashton Thorogood proposed a similar solution to my model, including all of the data definition language (DDL).

A similar solution using Object Role Modeling (ORM) is proposed by Gordon Everest:

First, you must isolate two object populations, as subsets of the CUSTOMER object. One is the population of all customers which are Gov’t agencies, let’s call them GOVT CUSTOMERS. The other is NON-GOVT CUSTOMERS. This can be done is two equivalent ways: (1) two subtypes of the supertype CUSTOMER, or as two objectified unary predicates on CUSTOMER. Now you have a predicate (relationship) verbalized as ‘CUSTOMER owns FACILITY,’ and another predicate: ‘NON-GOVT CUSTOMER may update FACILITY.’ Then you declare a subset role constraint, drawn as an arrow from BOTH the roles (NON-GOVT CUSTOMER and FACILITY) in the predicate (this is likened to a composite foreign key) to the same two roles in the ‘CUSTOMER owns FACILITY’ predicate. That effectively says that a NON-GOVT CUSTOMER may only update a FACILITY which they also own. The second rule is represented in a similar fashion. A subset role constraint is declared between the ‘GOVT CUSTOMER may update FACILITY’ predicate and the GOVT CUSTOMER owns FACILITY’ predicate. Only this time, the constraint is only on the GOVT CUSTOMER role (not composite with the FACILITY role). Effectively it says that a GOVT CUSTOMER may update any FACILITY as long as it is owned by some GOVT CUSTOMER.

Let’s get flexible!

Claire Nash chose to add extra flexibility to her design, accommodating different industries along with representing an ownership hierarchy within CUSTOMER.

She also chose to represent the rules through code and not on the model:

If the CUSTOMER is the FACILITY owner, allow the update.



and ULTIMATE_PARENT > 0, allow the update.

This solution also accounts for the fact that CUSTOMERs in 1 government agency cannot update FACILITY details for another government agency, say a British Government CUSTOMER updating a Canadian Government FACILITY (may not be an issue if your customers do not span that wide but leads me to).

Donna Bockian proposed a similar solution to Claire’s, allowing code to enforce the relationships on a more flexible model.

Jeff Lawyer suggested the following:

This may or may not be the best solution depending upon the unknown total business requirements, but I would err on the side of flexibility to handle potential exceptions to the information update “rule” stated.

On when to show the rules

Graeme Simsion:

I often participate in data modeling panels and get sent ad hoc questions like these. More than half of the questions I’m given can be answered by referring to two principles. One of them is:

One of the key decisions in information systems design is where to hold the rules: the basic options are (a) in the data structure; (b)in process / code (c) as data values and (d)external to the computerized part of the system.

In this case we could choose any one of the four – my guess is that the framing and context of the question (a data modeling problem!) will have led most readers to choose either (a) or (c) but it should be obvious that the other two options are also viable.

Many factors may bear upon the best choice: the stability of the rule is usually an important one. You should generally avoid building rules into data structure if they are liable to change during the life of the database. Another is the ease with which the rules can be implemented: some rules are difficult or impossible to implement in data structure for example. But the important thing is to recognize and evaluate the options rather than automatically use one method.

Don’t show the rules

Dave Hay:

This is an example of why you can’t put business rules in a data model. A data model can only describe structure–what is possible. With few exceptions, it cannot describe constraints.

As to your model, you should not have “Customer” as an entity class. The entity class is “organization”. One very reasonable sub-type of Organization would be “Government Agency”. You would then have the relationship, “Each Organization may be the owner of one or more Facilities.”

By implication, you could assume that the Government Agency would be responsible for the data captured about a facility, but that is meta-level information that doesn’t really go here.

You could add a “Facility Role” entity class, where each Facility Role must be played by one Organization and must be for one Facility. Each Facility Role is also an example of one Facility Role Type.

The documentation behind various Facility Role Types could describe your constraints, but that’s about as good as its going to get.

Barry A. McConnell:

Since all customers can update their own facilities, the ownership relationship already supports that rule. To support rights to another customer’s facilities you’ll either need a recursive relationship on customer to show a proxy editor (the preferred solution as it allows specificity and expansion of the rule later without restructuring) or you can just use the customer type attribute (which I’m assuming has been created or needs to be if not). The problem with this challenge is that a data model is a static view of data structure, but the question is one of functional procedure. The proper model for that is not an ERD.