June 1999                   Issue: 9

Journal of Conceptual Modeling
www.inconcept.com/jcm

Mapping Conceptual Names to Logical Names in VisioModeler
by Patrick Hallock

Introduction

The issue of mapping conceptual naming to logical or physical names has presented itself several times in the last few weeks. Therefore, this is a response to that issue as well as requests for more "how-to" type articles. The most influential reason for this article came during a DAMA meeting where a presenter noted that conceptual names were very important, but did not offer a solution for the mapping of conceptual names to the logical level. There are several options for controlling the naming between the conceptual and logical names in VisioModeler in support of Object Role Modeling (ORM).

Conceptual Names

In ORM, you create facts or assertions about objects and the roles that they play. It is best to keep these names as close to a natural language (for example, English) as possible. Here are some examples:

'Person has Last Name' (NOT 'Person has LastName')
'Status references Status Description' (NOT 'Status references StatusDesc' Or 'StatusCD references StatusDesc')

However, a short lesson on entities, references, and values in ORM may be appropriate. An entity type requires a name and a reference. The reference can be simple or compound. Simple references would be Person(id) or Status(code). A compound reference requires more than one identifier. For example, in order to identify a Room you must know the Building and the Room Number. Thus, Room Number partially identifies a Room, and Building(name) partially identifies a Room.

Value type objects are identified by their numeric to textual content. For example, the object Last Name may hold a value of "dela Cruz" or "Barden". The text of the name is the only identifier required. After all, I do not think we would assign an identifier to "Barden" other than "Barden" itself. As another example, in the fact type, ‘Item is sold in Quantity’, Quantity is merely a number such as 100. Although value types do not require a reference mode, we do want to control the logical name that is generated. Consider the fact type, ‘Payment has expected due Date’ with a second fact type, ‘Payment was received on Date’. In my example I am using Date as a value type object. I surely do not want to get a Payment table to two columns named Date. I want columns with names such as 'ExpectedDate' and 'ReceivedDate'. Further, I do not want to create many Date valued objects such as 'Received Date' either. In other words, we like the object ‘Date’, but some way to deal with the various logical names created by the use of the Date object.

Here is a quick list of options for mapping conceptual to logical names:

Here are some possible name using these rules:

Tip: As a side-note, new users of ORM may have noticed that names that contain more than one word are entered using brackets (such as [Last Name]) when using the ‘freeform’ method for fact entry in VisioModeler. Since names are noted with a capital letter, VisioModeler would assume to have two objects, ‘First’ and ‘Name’ without the brackets.

It is crucial to always control the naming at the conceptual level whenever possible. Not only will you get a consistent mapping, but also you will avoid a tricky little problem. The problem lies in the fact that if you change a logical name it will override the conceptual name forever. So what you may ask? Say you have the fact type, ‘Payment is received on Date’ which generates a logical column named ‘ReceivedDate'. Now, say you change the logical name to 'RDate'. Then, someone decides to change the fact to, 'Payment is expected on Date'. You might expect to generate a new column name of 'ExpectedDate' - well, it does not! Since you specified the logical name it remains 'RDate'. Once you start this cycle life it can be very difficult to consistently update both conceptual and logical names.

hallock1.gif (7736 bytes)

Notice that in the above figure we are in the Object Type properties window and 'Add to Object Type Name' is selected. This option will always create the logical name 'Personid'.

Rather than show a screen print for each option, let us just assume other options are selected for this entity.

If 'Do not use for naming' is selected, then the logical name is merely 'Person'. If 'Use as column name' is selected then, the logical name is 'id'. Note that this is not a reasonable option for this entity. After all, what 'id' are we talking about? Even with a table name of ‘Person’ a column of ‘id’ is not a good idea. The default option is 'Use Document's Setting'. You can set a global default for this setting at the document level by right-clicking on the white space of the ORM diagram and selecting "Document Options..." and selecting the "Misc" tab. You can then override this preference, if you wish, on an entity by entity basis.

Further, note that table names are taken from the entity that causes the table -- in other words, the entity having functional dependency control. Once the scheme is set for an entity type object it is consistent through out the entire logical model. This is just how I would want it - "Once Named Always Named".

hallock2.gif (1211 bytes)

Since 'Last Name' (above) is a value type entity there is no reference mode permitted. Therefore there is no name option mapping for the object on the Object Type Properties dialogue (the option is grayed out). We can control the name, but not from here.

Whereas the name control for entities is easy, the rest is are a bit involved. Below is the result of the fact type, ‘Payment(id) was received on the Date’.

hallock3.gif (1737 bytes)

The column named 'The received Date' is not what we want. We never want the word 'The' and let's assume that we want an abbreviation for 'received' to be 'Recvd' throughout the entire model. Both of these issues are solved in the ORM Document Options dialogue. (While in the ORM model diagram, right click in white space and then select "Document Options…".) Notice that on the "Abbrev" tab, our words are now available plus a set of default words that are abbreviated to nothing (blank). In effect this removes them from use in the naming scheme.

hallock4.gif (18065 bytes)

To make our changes simply remove the abbreviation for 'the' change abbreviate 'received' to 'recvd' as below.

hallock5.gif (18608 bytes)

By rebuilding the logical model, we get new logical naming conventions, below.

hallock6.gif (1656 bytes)

This process is usually the longest step required in mapping from conceptual to logical. You just keep at it until you have set all entities to a convention and then control the abbreviation option.

Tip: Here is a bothersome issue: notice the list is in the order entered in the system. Your daily additions to the model always appear at the bottom. I usually do not worry about naming until the model is fairly complete which causes the words to be in some impossible order to find. The way I fix the order to select all of both columns, copy them to the clipboard, paste them into Excel, sort the two columns in Excel, and then copy and paste it back into VisioModeler. OK, I can hear the screams from here - we asked to have this addressed, but have not received. Maybe in the next release we can stop this step. (Since the ORM model is in a database, I would think a "Select … Order by… " is not that difficult to do.)

Tip: As another side-note, note that the column names are in the order entered as well. If you want them ordered differently in the logical model, merely drag the column(s) to a new position. Then, save the logical model and they will stay in the new position(s).

This abbreviation method applies to both column and table names. Further, once an abbreviation is set for a word it is always used for that word. This is good to know, since some tools (or modelers, for that matter) do not maintain a consistent abbreviation for a word in the model. Once 'Received' is abbreviated to 'Recvd' it does not matter where the word is in the fact, the abbreviation is always consistent. In my opinion, this is better than the usual method of removing vowels from right to left which usually results in multiple abbreviations of the same word such as ‘Received’, ‘Recvd’, ‘Receivd’, ‘Recevd’, ‘Rcvd’, etc. This results in the programmer or query user never being sure what the abbreviation is.

A little bit needs to be said regarding table names versus column names. I may want the full word in the table name, but not in the column name. Say 'Person' is abbreviated to 'Pers' in 'PersID', which is OK, but a table named of 'PERS' would be better named 'PERSON'. Merely change the name of the table in the logical model. As stated before, the name is now set. For example, if you now change 'PERSON' to 'EMPLOYEE' in the conceptual model, the logical table name will remain 'PERSON'. You are stuck changing it to ‘Employee’ in the logical model yourself. Remember when dealing with logical names, "Once Overridden, Always Overridden"

Notice that our names are separated by a space 'Person ID' and 'Recvd Date'. Also the upper and lower case is what we typed. We can set options to globally control this as well.

hallock7.gif (26527 bytes)

Back in the ORM Document Options dialogue (above), set the Column and Table name options, as your standards require. I like the options that work like this: 'Status Code' becomes 'StatusCode'. So how to set that option? Use the "Misc" tab (below).

hallock8.gif (25855 bytes)

Several options are located here. Select the spacing option to create names like ‘Status_Code’ or ‘StatusCode’. The 'Reference Mode' options will set the document level default for the mapping of reference modes to logical names as discussed above. Maximum length should be as long as your DBMS or standards will allow. Note that this setting will start to chop off vowels right to left if the name exceeds the allowable length. Thus, this setting can yield random abbreviations, as I discussed above. Finally, you can pluralize your table names. For some, this is desired; however, ORM modelers tend not to do this. (Think back to a fact type, ‘Person has Last Name’, does not read as ‘Persons has Last Name’. Using example data the pluralized sentence is: ‘Persons with the ID "001" has the Last Name "Benson"’. The better reading is, ‘The Person with the ID "001" has the Last Name "Benson"’. I always opt for better readings.)

hallock9.gif (23934 bytes)

Column prefixes can either be set to the first "X" characters from the table name or a custom prefix for each table or column name. (I find them redundant.) Using prefixes, 'PayReceivedDate' is a substitute for 'ReceivedDate'. This dialogue box (above) sets prefix options for columns, tables, or both.

hallock10.gif (23467 bytes)

The suffix options (above) also apply to the table and/or the column names in the same manner as prefixes.

In conclusion, some last minute suggestions:

Patrick Hallock is a Senior Partner and Principal Consultant for InConcept. He has over 20 years of ORM/NIAM experience and is  a certified ORM consultant, trainer/train the trainer and a certified Visio trainer.

Contact Information:

Patrick Hallock
President and Co-Founder
InConcept
8171 Hidden Bay Trail N
Lake Elmo, MN 55042
path@inconcept.com
(651) 777-8484
fax: (651) 777-9634
http://www.inconcept.com

© Copyright, 1998-2004 InConcept (Information Conceptual Modeling, Inc.) All Rights Reserved. Privacy Statement.
ISSN: 1533-3825