Design

Design - Code Tables vs Consolidated Code Table

Topic:
RCTS as a dropdown showing titles (Mr, Mrs, etc), It also has a dropdown showing Reminder Period (1 day, 3 days, 1 week, etc), and a third table showing Requirement Type (Report, Payment Due, etc).

The question is should we create one table Domains(domain, value, title), or (in this example) do we create 3 tables: Titles(value, title), ReminderPeriod(value, title), and RequirementType (value, title)?

For RCTS a table called Program, would not be considered a Code Table – actually, while some pages within RCTS will treat the Program table as a code table, the Program object is a full-fledged managed set of data that falls outside the definition of a code table.

A table called ReminderType is a table containing just a description field, or a description field and a ID, or code. Because of its limited columns and potential use, it is safe to call this a Code Table (in the context of this topic)

We will consider two general strategies ‘Consolidated Code Table’ and ‘Code Tables’

You have a choice – should you make a code table editor per code type, or a giant code table editor to manage all the code types?

  • It is certainly easier to create a code table editor for each code table, and it is easier to create a giant code table editor to manage a consolidated code table. However, in theory you could do either.

Pro’s with Consolidated Code Table

  • If you have many tables, then this could save a lot of $ in development, especially if users will have code table editing capabilities.
  • Code tables are managed consistently

*** Pro’s with Code Tables

  • Frequently, functionality surrounding a code table expands, and then you start needing extra columns. Using separate tables allows this more easily.
  • It is easy to tune the table to meet the requirement, thus finer tuning of the interface.
  • When you study the logic associated with a code table, you start adding attribution, eventually it stops being considered a code table. For example, GLAccount number could start as a domain, then, it expands with a code categories, then balances, etc. At some point, this is no longer considered a code table. (So in an expanding project, a code table represents undeveloped opportunities :^)

Con’s with Consolidated Code Tables

  • Your locked into the limitations of the code table design, unless you change that design, then potential high cost to fix. Sometimes working around the limitation leads to esoteric solutions.

Con’s with Code Tables

  • Expensive to maintain in that there are extra tables, extra classes, extra UI
  • It’s easy to deviate from a standard design
  • If using a separate CTE per code table, then the creation of a new code table, could mean the creation of a new code table editor.
  • If you decide to stop using one of these then you are left with a clean process.

Case Studies

Maximo

Uses the term Domain to denote a potential Code table.

  • Maximo now has 4 code tables holding the different types of domains. Supporting 813 distinct domains.
  • Domain types include, Numbers (like priority value), String (like Material types), Ranges (which have 2 values),
  • Has 1 editor – you search for a domain and are presented with a popup of allowable values. Depending on the type of domain, that popup is a different program.
  • Impact of creating a CTE per table – would had been 800 additional programs added to the delivery. And an additional 800 tables in the database.
  • Was Domain Value Editor the right answer? Evidently Yes

CMMS

Uses a table per domain. 80 +tables. 80+ CTE’s.

  • CMMS addressed future expansion by adding an extra column called special. (ID, Code, Title, Special, Active) If functionality was expanded a bit, then the special column would be used. For example perhaps you have a table named Job Type, Special could had been used to hold an asset type.
  • Despite the existence of the Special column, 17 of those code table editors have additional columns to support activities associated to those codes that wouldn’t fit into the Special column.
  • The ut_activity table now has many columns and 5+ subtables
  • In this application staff wanted management of code tables so if we created a Domain Value editor then there would be complexities to 15+ % of those domains.
  • Was Code Table Editors the right answer? Yes

SASD - Employee System

  • 35+ tables, 34 editors – 50% of these have additional columns.
  • Used a code table editor per code table.
  • Was Code Table Editors the right answer? Yes

DMS

DMS – Uses a table per domain. 80+ tables.

  • I believe Less than 3 of those tables have ever had an additional column.
  • Was Code Table Editors the right answer? Probably not – this could had a common DomainValue editor for almost all code table editors.

Summary

  • Small projects – if the consolidated code table will work, then this is a good solution.
  • Project Rewrites – If the existing program has many code tables, (or will get a lot of code tables as a result of the rewrite) and the application is not susceptible to a lot of scope creep, and evidence suggests the design is stable, then a consolidated code table solution will work.
  • Big projects, or projects subject to scope creep – consider separate code tables, or develop a strategy when it is time to expand a code table.

So… Should I used separate code tables or a consolidated Domain table? Answer: That depends …. ;^)