By Ken North
Summary: VisioModeler is a database design tool that uses semantic modeling, a technique that permits database developers to produce a formal design by diagramming or verbalizing facts in an English-like language.
[Note: The first version of this article was a review published in Windows Tech Journal (September 1994). The fundamental points discusssed in the original article are still true, although I've revised some of the content due to product changes. InfoModeler is now VisioModeler, a Visio product and part of Visio Enterprise. VisioModeler added support for UML and IDEF1X modeling. Powersoft is now a Sybase company.]
Database Magic with Ken North provides more information about ORM and the techniques discussed here.
Some persons wonder why anyone would use the words database and formal design in the same sentence with Windows. But consider the statistics: Before Microsoft released Visual Basic 3.0, its market research showed that approximately 90% of VB developers were writing applications that used databases. The success of Powersoft PowerBuilder and Centura SQLWindows proves that Windows is muscling its way into client-server environments. In spite of doubts voiced by skeptics, it is no longer rare for Windows to be used to develop mission-critical, bet-your-business applications.
Using Windows to develop increasingly crucial classes of applications introduces several concerns. First, data access capabilities are becoming a more common part of the feature set for office applications such as word processors and spreadsheets. This introduces a whole new class of less sophisticated database users -- users who are more likely to introduce performance problems when querying databases whose design is less than optimal.
Second, the component-based architecture of many Windows developer tools makes it easy to build applications without acquiring the knowledge necessary to code the logic. This plug-in functionality is good news for budgets and schedules but potentially bad news when someone who doesn't understand the code builds a mission-critical application. A third concern is that Windows developers will pursue all available solutions -- design effort, custom controls and tools, and so on -- to create an application's user interface but will fail to dedicate comparable resources to database design. The result could be visually appealing applications that compromise data integrity and perform like a dog.
The dangers, then, are in using Windows' visual programming techniques that are too casual or informal for serious software development. If Windows is to support critical applications, developers must adopt formal techniques that provide integrity and robustness. This is particularly important where database applications involve bet-your-business data or performance that brings a system or a network to its knees.
Two formal techniques that developers can use to address data integrity in their applications are data modeling and information modeling. To ensure data integrity you must prevent anomalies that produce incorrect information. For example, you maintain referential integrity by avoiding orphaned tables. The process of normalization strives to avoiding anomalies, eliminate redundancies, simplify data retrieval, and maintain integrity.Normalization and data integrity are complex subjects that cannot be fully explained in a brief review. They are so complex, in fact, that developers often respond in one of two ways: They choose to ignore these subjects or use tools such as VisioModeler to refine their database designs. Which type of programmer would you hire to write your electronic funds-transfer software?
VisioModeler is based on the concept that information systems, when seen from different perspectives (user, manager, software developer), produce different views of the data. The user sees the external view - the information displayed by the application. The conceptual view is an articulation of information about the data using simple language. The logical view is expressed in terms of entities, relationships between entities, and attributes. The physical view is the actual database structure as typified by an SQL CREATE TABLE statement.
Users work with the external presentation of data but usually lack the skills to deal with the details of the conceptual, logical or physical design. One of VisioModeler's greatest benefits is that it gives developers a way to obtain design feedback from users. VisioModeler gives even those users who lack a technical background a way to make intelligent statements about the validity of fact descriptions.
Information modeling techniques let you model an application's data objects. The model includes types, entities, relationships, and properties, and it defines attributes such as uniqueness, rules, and cardinality. If your database projects are simple, such as a single table tickler list, you may not need a tool to create an optimal, normalized design. If your projects are more complex, you will benefit from a tool that helps to eliminate data integrity problems and validate a design.
The universe of modeling techniques and notations includes data flow diagrams (DFD), entity-relationship diagrams (ERD), and others techniques. VisioModeler uses Object-Role Modeling (ORM), a semantic modeling technique that views an application's universe of discourse, or area, as consisting of objects playing roles. If you want to study ORM, read T.A. Halpin's Conceptual Schema and Relational Database Design (WytLytPub).
VisioModeler implements Formal Object-Role Modeling Language (FORML) which treats a database as a collection of related facts. Working with FORML allows you to verbalize facts about a database design in sentences such as
Customer (code) has CustomerName
Every Customer has some CustomerName
Each Customer has at most one CustomerName.
In ORM, objects play roles within a domain and participate in facts. An object's arity expresses the number of roles it plays, so a unary fact is an object playing a single role. A binary fact plays two roles, ternary facts play three and quaternary facts play four. VisioModeler also supports binary ring facts (two roles, each played by the same object type) and derived fact types (calculations or logical derivations from other fact types). VisioModeler uses roles to create tables, keys and relationships between tables. To understand the total meaning of a fact, you must understand not only the object types and roles but also the rules or constraints limiting the roles that instances of an object can play.
You don't need a graphical user interface to design databases when using a method based on English-like sentences. Because VisioModeler is a Windows application, it gives you the option of using features that make it easier and more enjoyable to use. You can create a conceptual model in either verbal or graphic windows.
VisioModeler's Fact Editor shows the verbal description of facts, and the Diagrammer provides tools to draw, point-and-click, and drag-and-drop. VisioModeler lets you specify text in the Fact Editor and then generates the icon for the fact in the Diagrammer window. It also let you click on a fact in the Diagrammer window and view its text equivalent with the Fact Editor.
When you're working in the Diagrammer window, VisioModeler give you a Symbols and Constraints palettes to use while you define objects and roles and apply constraints. In the Symbols palette, you select the Object type button to create objects and click on the Predicate button to enter predicate text. After you've created two object types and a predicate, you select the Role Connector button to connect object types to roles. Next, you constrain the roles using the appropriate tool from the Constraints palette. The Constraints palette includes a variety of symbols to specify constraints -- mandatory role constraints, uniqueness constraints, index constraints, custom rules and so on.
Using VisioModeler means following a progression that creates a conceptual model, a logical model and then a physical design of your database. The first phase involves specifying facts by using the Fact Editor or Diagrammer to define objects and roles, apply constraints to those roles and enter a sample data population. After you've completed those steps, you can select a menu or toolbar option to validate the conceptual design and map it into logical tables.
Once you have a valid conceptual model to work with, you can select the Build Dictionary (logical model) button to specify details about naming conventions and to create tables. VisioModeler processes the information in the model about primary and foreign keys, external constraints and relationships (one-to-one, one-to-many, many-to-many). It then produces a Logical Model that you can view by selecting the Edit Dictionary menu option.
When you finish building the logical model, you can choose the Generate Database option to produce a definition of the database's physical structure. VisioModeler can generate a design for servers (Oracle, SQL Server), desktop databases (Paradox, Access, FoxPro), the Visual Basic Access Engine, or a generic SQL database. It recognizes product-specific features such as cascading referential integrity in Access or Transact-SQL triggers (SQL Server) and exploits those features when it generates the physical design. The SQL scripts for Oracle and SQL Server, for example, will include constructs such as CREATE TRIGGER, that represent product features not available in the generic SQL output. The VisioModeler documentation includes information about the differences between drivers for desktop and server databases.
VisioModeler also has sufficient product knowledge to warn you of potential problems as you generate a physical design. When creating example databases used for a book about multidatabase programming, I created a single logical model to generate databases for FoxPro, Access, and several SQL DBMS products.
When generating a FoxPro program, VisioModeler warned me that my application would have to enforce referential integrity because FoxPro does not provide referential integrity at the engine level. When generating an Access database, it warned me about certain uniqueness constraints on composite keys that would have to be enforced at the application level and not in Access. There were a few minor glitches with respect to character types, but VisioModeler is clearly a useful tool for generating database designs for applications that run against more than one DBMS.
VisioModeler's Table Browser give you condensed views or expanded views that let you examine details of each column in your tables. VisioModeler includes a repository for global and local objects and an Object Browser that lets you import global objects into a model. The product's version control features permit a modeler to publish drafts and use a working model as well.
One limitation of the first versions of InfoModeler was a Maintenance menu that provided the ability to delete models but not to delete one or more drafts of a model. Later versions corrected the problem and dropped a network model database used to store objects, which occasionally required verification and reorganization.
VisioModeler is a useful tool for database developers, but it is not a lightweight application. The minimum recommended configuration is a Pentium with 16 MB of RAM. In my experience, you want to throw as much memory as possible at real-world modeling problems. The first version (1.01) lacked import and export facilities, but that was changed in subsequent versions. You can now export ORM models to Visio Enterprise, for example.
VisioModeler is certainly a product for prime-time database design. It merits the attention of serious database developers, particularly those building mission-critical databases.
Want more information? Read "Modeling, Metadata, and XML" (Web Techniques, June 1999).
Ken North is a software consultant and the author of Windows Multi-DBMS Programming (John Wiley & Sons) and Database Magic with Ken North (Prentice Hall). He also teaches Expert Series seminars.
Publications
Data Access Roadmap
Ken North Home Page
Revised: August 1, 1999