One of the fundamental assumptions I make about any modelling tool is a simple one, that the metadata it presents to me reflects the actual metadata held by the tool. Sometimes, that metadata can be viewed in several ways, and sometimes one item of metadata is inextricably linked to another item of metadata. For example, when I look at the optionality of a foreign key attribute or column, I expect it to match the optionality of the relationship through which it migrates.
In all the tools I can remember, there are four ways to view this optionality:
- via the properties of the attribute or column
- via the properties of the relationship
- via the diagram symbols that represent that relationship on one or more diagrams
- via the attribute properties displayed in an entity symbol.
If I need to know the optionality of a particular foreign key attribute, I ought to be able to look at any of these things to see the answer, knowing that they will all give me the same answer.
Well, recently I checked through a PDM, and noticed that a relationship was mandatory, though the foreign key column was optional. Eh?
So I checked the rest of the model, and sure enough, there were more. There were some relationships that were mandatory according to the ERD, yet optional according to the relationship’s properties. I was using ERwin 7.3.11, but r9.0 behaves the same way.
Here’s an example that anyone can replicate, using the r9.0 Community edition. Just look at the supplied ’emovies’ model, and you’ll see at least one example. There’s a recursive relationship on Employee, which migrates the employee number into the ‘supervisor’ attribute. The relationship is optional, yet the ‘supervisor’ attribute is mandatory.
If you want to create your own example, copy the CUSTOMER CREDIT and PAYMENT entities to a new model, and remove most of the attributes (for clarity). The circled items show several views of the relationship/attribute optionality, and they all agree.
Now use the attribute editor to change the ‘null option’ for the FK attribute, to ‘Not Null’. The attribute becomes mandatory, but the relationship is still optional.
This must be a bug, I thought, and has probably been fixed by now, so I contacted our local reseller, who said [I’ve paraphrased what they actually said] “Oh, we know about that, we raised it with CA and they said that it’s meant to work that way, so we’ve raised an enhancement request to change it.” I was speechless! Why would anyone want it to work that way?
Ok, some of you may think that I’m making this up, after all I have a well-documented (about 550 pages) bias towards a competing product, so try it for yourself, and decide whether or not you can live with this ‘feature’.