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’.
After having a quick look at this post, it seems author is confused about null-ability of a column and cardinality of a relationship.
Thanks for the comment, Harry. Please take a detailed look, and you’ll see that I don’t mention cardinality at all. I circled the parent end of relationships to highlight the nullability.
George, ok got it!
Based on my testing, what’s shown in diagram accurately reflects the OPTIONALITY of relationship (what you see in relationship editor).
If you want to make the relationship OPTIONAL and have the child columns semantically in sync with relationship, I recommend you to change the OPTIONALITY of relationship instead of over-riding NULLABILITY of child column(s).
This behavior has an obvious merit that I can document a relationship semantically mandatory although I may have to relax the NULLABILITY of child columns for practical reasons.
Hello Harry
I would never want the relationship and FK column optionality to differ within a model – the columns will be mandatory or optional because the relationship is mandatory or optional. If the relationship is semantically mandatory but physically optional, then I expect the relationship to be mandatory in the LDM and optional in the PDM. ERwin users will realise that this is impossible to show in one ERwin data model, as there is only one model – the Logical and Physical views are exactly that, different views of the metadata for different audiences, not different models. This is one of a several reasons why I could never recommend buying ERwin.
George
Hello George, Appreciate your response!
I still believe column NULL-ability should not dictate the relationship optionality either on LDM or PDM.
How do you see the situation if the relationship had multiple columns?
-Harry
George,
Difficult to digest some of your thinking especially when you say “If the relationship is semantically mandatory but physically optional, then I expect the relationship to be mandatory in the LDM and optional in the PDM”.
When we say relationship is semantically mandatory, “relationship is mandatory”. It is not optional either logically or physically.
Let column NULL ability complement/support relationship optionality but not dictate.
NULL-ability of a column that refers another column cannot be the final word to call relationship optional/mandatory.
About tools behavior, when I make a relationship mandatory it should automatically change the child column to NOT NULL. But if you want to change the column back to NULL explicitly, that can be allowed. No big deal!!!
1) Practically its just about having columns that must have value but don’t enforce me to INSERT in the first go.
2) Relationship to composite KEYS where one column is OPTIONAL.
-Harry.