*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
Breck Carter
Last modified: June 11, 1996
mail to: bcarter@bcarter.com
Figure 1 shows a straightforward arrangement of two code-and-description tables for accounts and divisions with foreign key relationships to columns in the simple_child table.
When the number of code values explodes so do the number of tables and
foreign key relationships. The database diagram becomes unwieldly and
the task of building code update windows becomes repetitious. Many
applications have several dozen different code types, and I've seen one
system where the number approached 600.
This explosion often leads to efforts to simplify the database design by
combining the separate code values into a single table. Figure 2 shows
what such a combined code table might look like complete with the
equivalent foreign key relationships to a table called complex_child.
The name "polymorph" means "many forms" and the table in Figure 2
does indeed take on multiple meanings: If you're looking for account
descriptions then it's the account code table; if you want divisions
then it's the division table... it's whatever you need it to be.
It works as follows: The primary key to polymorph contains two columns,
one identifying the type of code and the second containing the actual
code values. For example, all the account codes contain "account" in
the code_type column.
While the polymorph table does reduce the number of tables in the database, it causes many problems. The first is the difficulty in defining and maintaining foreign key relationships: Each relationship now involves two columns instead of one and the application must take care to fill account_code_type with "account" and division_code_type with "division".
This difficulty often causes designers to omit the foreign key relationships altogether simply to avoid defining these extra columns. Referential integrity constraints are a good thing, they catch difficult bugs early in the development process, and the more complex a system is the more one needs this benefit.
In the 600 table case the problems are severe. However, that also means you need all the help you can get. In other words, omitting foreign key relationships is a bad thing.
End users are completely baffled by the polymorph table. With separate tables (as in Figure 1) and decent table and column names they can get by with little or no documentation. Query tools often make good use of natural joins and can lead the user by the hand to include description columns in the result set.
On the other hand, the polymorph table requires separate documentation and in some cases a training course to explain what's going on. Programmers need this too, and it's safe to say that when programmers have trouble figuring out the database the end user is unlikely to fare much better.
Sometimes a particular code table needs extra columns. With separate tables this is no problem whereas with the polymorph table it's an all or nothing affair: All the code types get the same data columns with the same data types.
The main problem with separate code tables is one of repetition and boredom, not complexity. The creation of code table update DataWindows is a simple task, as is the definition of code-specific DropDownDataWindows. The techniques are different from those used with the polymorph table but no more difficult.
Fewer tables do not necessarily make the design simpler. Additional
layers of abstraction do not always make life easier. Sometimes
K.I.S.S. is better.