Wednesday, 07 March 2012 04:16

Data Modeling Antipatterns - Part 2 of 2

Written by
Rate this item
(0 votes)

An antipattern is a characterization of a common software flaw. An antipattern shows what not to do and how to fix it. As you construct data models, you should be alert for antipatterns and correct them as they occur. These videos show several examples of data model antipatterns and applies antipatterns to the LDAP case study.

The first EAP file is the database schema for the LDAP product. As the video explains there are 11 tables. The tables clearly define primary key field(s) and there is a consistent naming style. For example, by inspection one would suspect that ClassContainers.clsID and ClassContainers.containerClsID both refer to the Classes table. Examination of the data confirms this supposition. (There are a number of records and all the values of the presumed foreign keys are covered by referenced primary key values.)

A brief comment about notation. The legend "[1..1]" refers to attribute multiplicity -- a minimum of one value and a maximum of one value. When attribute multiplicity is omitted, then the default applies (minimum of zero and maximum of one). The legend "{pk}" means that a field is part of a primary key. All tables have a single primary key field, except for ObjectAttributes. The data types are self evident.

The second EAP file is the database schema after reverse engineering. I deduced foreign key on the basis of suggestive names and confirmed them by data analysis. The foreign key referent is mandatory or optional according to the nullability of the source attribute. Association end names indicate that the foreign key name differs from the referent primary key. The source of identity for ObjectAttributes is complex -- it combines ObjectLookup and Attributes and also requires a third value (sequence) that the qualifier indicates. The use of association classes and qualifiers during reverse engineering is common -- unfortunately many developers do not use object identity and they propagate identity from the data sources. A better schema for ObjectAttributes would have a primary key of objectAttributeID. Then the combination of aID, dsID, and sequence could be indicated with an alternate key.

This is part 2 of 2 in a video series.

Read 7402 times Last modified on Tuesday, 16 April 2013 00:46

Michael Blaha

Modelsoft Consulting Corp (Consultant)

Since 1994 Michael Blaha has been a consultant and trainer in conceiving, architecting, modeling, designing, and tuning databases. He has worked with dozens of organizations throughout the world. Blaha received his doctorate from Washington University in St. Louis and is an alumnus of GE Global Research in Schenectady, NY. You can contact him at
More in this category: Colour your Knowledge »
Login to post comments