Table of Tables Explained

What is a table of tables?
Table of Tables is the use of a single two or three keyed value reference table to emulate many small, single key, field value reference tables. Looking at the commonality of field checks within any application there are only certain forms of checks that happen on them. There are free format fields which are only limited by size and character set allowed within them. There are field that are limited within ranges of values (i.e., -78.6 to 99.7 or A to F). Finally there are the fields that are filled from lists of allowed values. It is this third set of validations and set controls that the Table of Tables concept works to facilitate. Turns out 80-95% of all list reference checks have the same core functions:

• Is it a valid value - Yes or No;
• Get the default value that should be used if nothing else provided;
• List what the valid values are in simple order;
• List what the valid values are in some optimized order;
• Allow values to be added and removed from the set;
• Provide and manage the meta data on each set;

Why use a table of tables?
Let's be honest with ourselves. We all have reasons to do things quickly. Tables of Tables is one of those few things that allows all levels of a development effort to get what they want.

• Programmers are essentially lazy and will do things quick and easy if the impediment to doing it well is time. If doing a verification is easier then avoiding it then it will get done. If it is also a glaring spot in a code review it will get done.
• Projects want quick in order to not add costs and time to a development effort especially when the added value is considered minimal. Table of Tables reduces thousands of stored procs and lines of code to ten (see below).
• Application owners want small because small can be maintained as easily on Day 1 as on Day 1000. If the references are dynamic and the support for the references are robust then keeping a system/application current will be simpler, more straight forward, more reliable and adaptable to the changes of the system.
• If almost everything is verified in a consistent and complete manner then the data cleanliness of the application will be better and so data cleaning costs will be reduced for the organization.

In a project where there are 1000 reference tables the repetative task of building the tables, stored procedures for working with every table, managing every table becomes a huge amount of work and maintenance and cost. Multiplied out for the application the a print/report function, an add/update function, a verify function, a drop down function adds up. If your application had potential of 1000 reference sets this would be traditionally 1000 manage functions, 1000 print functions and 8000 stored procedures for use and maintenance. Especially for a 2-10 value reference set would not be worth the effort and so would become in reality hard coded. With a Table of Tables capability in place it is easier to use the common form for those 960 cases( 2 iterations of Pareto's Assertion is 96% ). It is easier to ensure that the references are fully dynamic through the common process. Then, only when necessary, for the remaining 40 cases does the full process become necessary because that particular reference set is different enough to make it worthwhile. Note if performance becomes an issue with just one table of tables it is easy enough to have a second or third table of tables as needed. If some objects can be controlled at the day level then and some are needed at the second level then two table of tables instances may be worthwhile.

You will also hear....

Built in Referential Integrity checks within databases cannot handle this way of working.	My perspective is that they can be made to work with some careful thought that the primary unique key in a TableofTables is with the proper SetCode of the field and not NULL for everything.
How will this perform when it has hundreds of sets within it.	Database tables are built for handling 10s of thousands of records. A Table of Tables , if it has 1000 sets of values and the average number of values to a set is 15 but ranges from 1 to 200 then we are still only talking about 15,000 records. Well within what a typical DB table is capable of. We built this in Business Basic and the TableofTables File was loaded once and held in RAM for sessions rather than for every call. The stored procedures were likewise loaded and prepared once. This ended up being a 1000 fold performance improvement over an individual file situation. At the time a file and index load was measured as 20,000 CPU cycles and unload of 10,000 CPU cycles with about 1000 CPU instructions to do the find and response. This 30,000 CPU cycles was for every field vs the TableofTables result which was the 1000CPU instructions for the check of only slightly larger index.
Can you get redundant / duplicate Code Sets?	Absolutely this is a data management consideration. The question then is that the set MetaData needs to help avoid this by having good descriptions and help map when valid reasons for this does exist. Guess what that is all within the Codes set ZZZSETDESC. One place.
How can upcoming roll-outs of changes be handled with the Tableof Tables?	Set Values can get rolled out to an end environment in advance of the software to handle it as long as the EffectiveEndDate on a record is set in the past. A quick data tabel update to reset the values that are in place to have a blank EffectiveEndDate then make them valid values without any transmission and load time delays. In some situations this is why having the EffectiveEndDate as a Date and Time fields will give you release control to the second rather than just at midnight.
Can this be used too much?	In my experience a table of tables allows anything that would otherwise get hard coded, as a value or length, to be dynamically held and referenced. In a manufacturing product development situation we set Table of Table values for the lengths of fields that were core to our effort. An example was to set the "Part Number" length to a maximum size of 20 characters. We put it in the table and dutifully referenced it whenever we needed to but never expected it to ever change. But sure enough on our 15th customer installation they needed/demanded a Part Number length of 27 characters. Had we been completely true to our goal of referencing the dynamic value whenever we needed it? The test was that we shifted that customer install to 30 in the table reference and tested. One report no longer formatted properly because it used the new value and that put the printing beyond the page width allowed by the printer. Remember - That which you believe cannot happen WILL!!!

What are the minor additions to fulfill the 2nd iteration of Pareto's Assertion (80:20 rule)?

• ZZZSETDESC ZZZSETDESC record. - This gives you a referential integrity point to the uniquness and spelling of the SetCode field.
• Effective End Date - this gives you the ability to "soft delete" a value from the set without destroying referential integrity to the values that should be updated but are still in place.
• Order Number - this gives you the flaexibility to prepare your drop down order in a way other than alpha sort optimized for the situation and expectations.
• Other Info string - this give cross reference and supporting values CA=CAD US=USD FR=EURO without having to do othertable checks. If your cross reference needs are greater then full consider an expanded common relationships table.

Structure of a table of tables
SetCode SetValue OrderNumber EffectiveEndDate Description OtherInfo
Table Field Descriptions

SetCode	10 to 19 characters	(In some ERP systems you will see this as int or doubleint)[this is small and efficient but a a true pain to utilize as a developer]
SetValue	10 to 19 characters
OrderNumber	Integer	Special Order you wish values shown in
EffectiveEndDate	Blank or a Valid Date.	In some rare situations you may want this to be full Date and Time. After this date the Set Value is no longer valid for verifications of new or modification of entry.
Description	15 to 30 characters free format	describe what the set value stands for i.e.,CA=Canada vs CA=California. This will typically be shown in any drop downs or value presentations you might display.
OtherInfo	15 to 30 characters free format.	My experience is that a cross reference value can be useful i.e., CAD with CA - USD with US... typically this should be something structured info and parsable.

Primary Index	SetCode + SetValue
Index 2	Setcode + SetValue + EffectiveEndDate
Index 3	SetCode + OrderNumber + EffectiveEndDate

Initial Record Insertion prior to Integrity Checks
ZZZSETDESC ZZZSETDESC 1 {blank} Set_MetaData_to_each_SetCode {blank}

Stored Procedures for a Table of Tables

Add/Modify a Record	SetCode is found in the ZZZSETDESC table set OrderNumber is a valid integer or doubleint EffectiveEndDate is blank or a valid date.
Display Set Alpha Ever Records	Select * from TableofTables where SetCode = Parameter1 sorted ascending by SetValue
Valid Value Ever Check	Select SetValue from TableofTables where SetCode = Parameter1 and SetValue = Parameter2 If Found then return TRUE If Not Found then return FALSE
Valid Value Date Check (aka. Value Value Now Check) High Usage for field entry checking	Select SetValue from TableofTables where SetCode = Parameter1 and SetValue = Parameter2 and (EffectiveEndDate is blank or EffectiveEndDate GT Parameter3) If Found then return TRUE If Not Found then return FALSE Note: A processing version of this with OtherInfo in Fields list should be considered.
Display Set Date Alpha High Usage for dropdown presentation	Select SetValue Description from TableofTables where SetCode = Parameter1 and (EffectiveEndDate is blank or EffectiveEndDate GT Parameter3) sorted ascending by SetValue
Display Set Date Optimized High Usage for dropdown presentation	Select SetValue Description OrderNumber from TableofTables where SetCode = Parameter1 and (EffectiveEndDate is blank or EffectiveEndDate GT Parameter3) sorted ascending by OrderNumber
Get Default Set Value High Usage to grab default values	Select SetValue Description OrderNumber from TableofTables where SetCode=Parameter1 and SetValue = and (EffectiveEndDate is blank or EffectiveEndDate GT Parameter3) and OrderNumber = 0

How to use it?

• Have a new Set that you wish to create, validate and display?
  
  • Check if the code you need is already present. If not then Pick a unique SetCode.
  • Add a record to the table of tables that is ZZZSETDESC SetCode int {blank} Set_metadata Other_Information_like_where_used.
  • Add SetValues with the now valid SetCode. Set the OrderNumber in an optimized display order for a situation that it is to be used. Canada, United States and United Kingdom may wish to be the first three contries in a country list so they might get 2 4 and 6 as order number respectively while all other country codes are from 10 and up in increments of 5 (sorry- I was a Fortran / BASIC coder at the start).
• Once a set is in you can not use $valid = Valid_Value_Date(SetCode, InputValue, Today()) to get true or false.
• You an use $droplist = Display_Set_Optimized (SetCode,Today()) to populate your droplist
• Or you can use $droplist = Display_Set_Alpha (SetCode,Today()) to populate your droplist.
• And $defaultValue = Get_Default(SetCode,Today()) to retrieve and populate a default value for a field. The SetCode is the only dynamic thing to remember for all these situations.

Return to the Blog Topic Page
Return to the Main Index
Send me a Comment
Update 2021-02-01  rwh