More low hanging fruit...

Value constraints limit the allowable values on an object type. For example, you can limit the values of the Sex(code) object type to be “M” or “F” (*), the values of a Rating() object type to be between 1 and 5, or the values of a SQLChar() object type to be “A-Z”, “0-9”, “a-z”, and “_”.

You set these in the Value tab of the object type’s Database Properties sheet. On that tab, you’ll note that you can enter distinct values, ranges, or a combination of both (as is the case for our SQLChar() object type above).

If you have more than 5 discrete values/value ranges, you’ll notice that they don’t all show up on the diagram. In VisioModeler/InfoModeler, you could alter the number to display for each object, and you might be wondering where that option is in Visio, since it isn’t on the Value tab of the Database Properties sheet.

To set the number of allowable values to display for a given object, right-click on the object and select “Shape | Custom Properties...” (or select the shape and choose “Shape | Custom Properties...” from the menu).

One final note: value constraints get generated as check clauses in your logical/physical models. This means that any change to a given allowable value would require a schema change. For example, if you define a Department(name) object type to be limited to “manufacturing”, “sales”, and “administration”, you will need to alter the schema when management splits the “sales” department into separate “sales” and “marketing” departments.

Another way to implement this is to constrain the value via meta-data in fact type (i.e. as a lookup table). In this case, you would have a fact type like “Department(code) is also identified by Department Name()” (**). This will cause the generation of a Department table that you would use to maintain department codes and names. This way, when the above reorganization happens, you merely enter a new code-name pair for the marketing department (and migrate any referencing data values as well, of course).

That aside, I like to use value constraints when I am in the analysis stage and then convert them to lookup tables (as above) during the design stage. The reason for this is that it reduces clutter in the model during this phase (when you are trying to review facts and core business rules), and they show up nicely in fact/object reports for said review.

(*) Depending on your Universe of Discourse (UoD), of course. When doing a model for a health care provider, I was privy to a 2-hour discussion about whether or not the database should allow other Sex Codes.

(**) Don’t forget the uniqueness constraint on the Department Name role. Department code is essentially a surrogate identifier, which means you better have at least one other unique identifier. More on this concept later when I talk about surrogate keys.