OpenInsight's MultiValue Advantages over Third Normal Form SQL
This white paper compares Revelation Software’s implementation of a technology known as MultiValue to the more commonly known relational database design paradigms.
OpenInsight™, a database application design tool from Revelation Software contains its own embedded database which uses a technology known as MultiValue. The basic idea behind MultiValue is that fields can contain more than one value of data. For example, rather than creating separate fields for phone numbers such as Phone1, Phone1_Type, Phone2, Phone2_Type, Phone3 and Phone3_Type, an OpenInsight table would have two fields named Phones and Phone_Types that can store either none, one, or many values of data.
The following is a scenario that could be used for a discussion of how OpenInsight could simplify a data design, thus simplifying development and maintenance. In fact, the process of system implementation (table by table and relationship by relationship) should be thought of as both the implementation and system maintenance: system requirements change over time. This clearly makes development and maintenance part of a continuum. OpenInsight excels at scaling for complexity, the real-world type of development and change that takes place over the lifespan of system.
This scenario would contrast the approach taken by standard relational (or third normal-form) systems and OpenInsight.
Rules of Normalization
There are, as of 2005, six normal forms. For the purposes of this document we will refer to them as 1NF for first normal form, 2NF for second normal form, etc. We will also restrict our discussion to the first three normal forms, as it is these that are typically used in relational database designs.
Rules for the 1NF state that duplicative columns must be removed from the same table and that separate tables must be created for each group of related data and identify each row with a unique column (the primary key). Rules for the 2NF must include all the rules of the 1NF, plus the removal of subsets of data that apply to multiple rows of a table and placing them in separate tables. Relationships between these new tables and their predecessors must be created through the use of foreign keys. 3NF tables must follow the rules of both 1NF and 2NF tables, plus the additional rule of removing columns that are not fully dependent upon the primary key
This is where SQL really hits its stride, but it is expensive to design and maintain, and often results in the need for triggers to be placed about the database tables. The following scenario will explain this.
Sample Scenario
An attached Sample Scenario shows a specific example with 4 employees, 2 departments, and 2 projects:
OpenInsight: 3 tables with 8 rows; 1 index with 10 values.
3NF-SQL: 6 tables with 27 rows; 12 indices with 48 values.
It highlights how the two systems attempt to model the types of entity relationships that are available (one-to-one, one-to-many, many-to-one, many-to-many, recursive).
In particular, it shows how OpenInsight allows a simple data model, based on the way people think.
SCENARIO:
Business Rules:
There is a business.
The business has EMPLOYEES.
The employees have PHONES.
Each employee is in a DEPARTMENT.
Employees may be assigned to PROJECTS.
Each employee has a manager, in a hierarchy up to the president.
The following illustrate some development needs over the lifetime of the system:
1. The business wants to catalog its EMPLOYEES, keeping track of with all the standard employee information. Each employee is assigned a key: employee number.
For OpenInsight, there is an Employee table, indexed on each name. Thus, "van Wagner, John (aka Jack)" is indexed under "van", "Wagner", "John", and "Jack" . The parentheses and 'aka' are thrown out. There is one index, on name. Lookups are available on each name.
(OpenInsight: There is now one row for each employee - indexed once)
For 3NF-SQL, the Employee table is indexed on name. This means that "van Wagner, John" is indexed under "van Wagner" at best, under the entire name at the worst. There are two indices, one on the key (to assure uniqueness): employee number, one on name. Lookups are more limited than with OpenInsight.
(3NF-SQL: There is now one row for each employee - indexed twice)
2. The business now wants to be able to contact each employee by PHONE. Each employee may have several telephone numbers (one-to-many relationship), each associated with a phone type (desk, home, fax, home fax, cellular, etc.).
For OpenInsight, a phone MultiValued associated group (MVAG) is added to each Employee row; it contains phone number and phone type. No table reorganization is needed.
(OpenInsight: There is still one row for each employee - indexed once).
For 3NF-SQL, there is a Phone table containing the employee number, phone number, and phone type for each phone. It is indexed on the key (to assure uniqueness): employee number plus phone number and on employee number.
(3NF-SQL: There is now one row for each employee - indexed twice PLUS one row for each phone number - indexed twice).
3. The business now wants to deal with its structure, identifying DEPARTMENTS. Each employee belongs to a single department (many-to-one relationship). One of the employees is identified as the department supervisor (one-to-one relationship).
For OpenInsight, there is a Department table containing the department number and the employee number of the supervisor. Because there are so few departments, it is not indexed on department at all. To support relational cross-reference (Xref), a department column is added to each Employee. Employee and department are now automatically linked. No table reorganization is needed.
(OpenInsight: There is now one row for each employee - indexed once PLUS one row for each department).
For 3NF-SQL, there is a Department table containing department id and the employee number of the supervisor. It is indexed on key (to assure uniqueness): department id.
(3NF-SQL: There is now one row for each employee - indexed twice PLUS one row for each phone number - indexed twice PLUS one row per department - indexed once).
A department column is added to each Employee row, involving a reorganization of the table. This column is indexed.
(3NF-SQL: There is now one row for each employee - indexed three times PLUS one row for each phone number - indexed twice PLUS one row per department - indexed once).
4. The business now wants to deal with its PROJECTS. One employee is identified as project manager. Projects may span departments. A project may include several employees; an employee may work on several projects (many-to-many relationship).
For OpenInsight, there is a Project table, containing the project number, the employee id of the supervisor, and a MultiValued column of all the employees assigned to the project. Because there are so few projects, it is not indexed on department at all. To support relational Xref, a MultiValued column for Project IDs is added to the Employee row. No table reorganization is needed.
(OpenInsight: There is now one row for each employee - indexed once PLUS one row for each department PLUS one row for each project).
For 3NF-SQL, there is a Project table containing each project number and the employee id of the supervisor. It is indexed on key (to assure uniqueness): project id.
(3NF-SQL: There is now one row for each employee - indexed three times PLUS one row for each phone number - indexed twice PLUS one row per department - indexed once PLUS one row per project - indexed once).
A Project:Employee table is created. Each row models the many-to-many relationship of project id to employee id. There is one record for each such relationship. It is indexed on key
(project id + employee id), on project id and on employee id.
(3NF-SQL : There is now one row for each employee - indexed three times PLUS one row for each phone number - indexed twice PLUS one row per department - indexed once PLUS one row per project - indexed once PLUS one row per projectemployee - indexed three times).
5. The business now wants to understand how employees relate to each other. Although each employee is in a single department, a department has its own hierarchical internal structure with each internal employee reporting to a single intermediate manager. Even a department supervisor reports to another employee. Only the company president does not report to a supervisor.
The company is considering a matrix organization where each person might have several managers.
Thus, there would be a many-to-many relationship among employees and employees-as-managers.
However, for now there is a many-to-one.
For OpenInsight, there is a column added to each Employee row, identifying the manager. Also in each
Employee row, a MultiValued column of all employees to whom this person is a manager provides for relational Xref. No table reorganization is needed.
To take care of matrix organization, the manager row would be changed to a MultiValued column. No reorganization would be needed.
(OpenInsight: There is now one row for each employee - indexed once PLUS one row for each department PLUS one row for each project).
For 3NF-SQL, there is an Employee:Managers table, containing the relationship among employees and managers (also employees) (many-to-one); this allows for the future matrix organization. It is indexed on key (employee+manager), on employee and on manager.
(3NF-SQL : There is now one row for each employee - indexed three times PLUS one row for each phone number - indexed twice PLUS one row per department - indexed once PLUS one row per project - indexed once PLUS one row per projectemployee - indexed three times
PLUS one row per employee: manager - indexed three times).
6. This is modeled as a many (employee) to one (manager) relationship. However, from a retrieval point of view, we really want to derive the entire business hierarchy, starting with the president (recursive relationship). We have the data necessary for such a process in this table; however, SQL does not provide for such processing.
The 3NF-SQL system cannot handle such a Bill-of-Materials relationship at all.
The 3NF-SQL system development stalls due to overwhelming complexity. The OpenInsight system (like the Energizer bunny) is 'still going'.
The above scenario envisions only two minor business requirement changes; experience tells us that many more usually occur.
Other issues:
These scenarios have still not dealt with issues of:
Disk Usage: How much space is consumed by fixed length columns? How much by duplicated data and keys from multiple tables? (Granted, not very important in today’s gigabyte servers, but in handheld devices critical.)
I/O Complexity: How much input/output (data and indices) is required to gather and scatter the data from multiple table rows for a single conceptual operation (e.g. read an employee and all phone numbers)?
Referential Integrity: What happens when we delete an employee? What else must be deleted and/or changed?
Programming: How complex is the process of designing screens and reports? What can be left to the user as ad-hoc versus how much must be set in stone?
Data Entry: How complex are the various screens, each containing data from many rows in many tables? What does a user need to know about navigating through them?
Reporting: What does it take to get a printout of the various relationships? How much I/O is involved?
For browsing, how can a casual user get complex presentation of data (as with a virtual column that packages a lot of information in a multi-line display field)?
Changes: Further changes to the database, each involving table reorganization - a potentially fatal situation.
Changes: Changing a single value (1NF) column in a row to a multi-values (requires, at the very least, a new table, new screens, and new reporting)
Changes: Adding just a new column can require the shutdown of the system, adding of the new column in the base table and in any and all related tables. Requires downtime, late night changes, etc.
All the above issues are real and deal with real costs (and backlog pressures) in terms of system design, implementation, maintenance, and usage.
These issues are reasonably technical. The above scenario would be well understood by anyone who has worked with such 3NF-SQL systems.
For those who haven't, ask the following question: "Do you keep a rolodex of people cards and a rolodex of phone number cards and a way of correlating them OR do you keep a rolodex of people cards with one card per person, including a list of phone numbers?"
In OpenInsight, the list is a base data type. In 3NF-SQL the list is derived (if possible) by joins at run time.
OpenInsight vs. 3NF SQL:
It's the difference between a Swiss Army knife and a cleaver.
Sample Scenario OpenInsight: 3 tables with 8 rows; 1 index with 10 values
OpenInsight Employees: 4 rows, Indexed on Name (10 Values)
| Employee ID | Name | MV: Phones | MV:Phone Types | Department | MV: Projects | Manager is | MV: Manager of |
| 1 | Van Wagner, John (aka Jack) | 555-1111
555-2222
555-3333 | Work
Home
Fax | Sales | Jones
Smith | 2 |  |
| 2 | Browne, Arlene | 555-4444
555-5555 | Home
Work | Sales | Smith | 3 | 1 |
| 3 | Carruthers, Sandy | 555-6666
555-7777
555-8888 | Work
Cellular
Fax | Sales | Jones | 4 | 2 |
| 4 | Jameson, Kim | 555-9999 | Work | President | Jones
Smith | 4 | 3
4 |
OpenInsight Departments: 2 rows, no indexes. Use employee list here as an index to Employee by Department
| Department ID | MV: Employees | Supervising Employee |
| Sales | 1
2
3 | 3 |
| President | 4 | 4 |
OpenInsight Projects: 2 rows, no indexes. Use employee list here as an index to Employee by Department
| Department ID | MV: Employees | Managing Employee |
| Smith | 1
2
4 | 3 |
| Jones | 1
3
4 | 3 |
Sample Scenario 3NF-SQL: 6 Tables with 27 rows; 12 indexes with 48 values
3NF-SQL Employees: 4 rows, Indexed on Employee ID and on Name
| Employee ID | Name | Department |
| 1 | Van Wagner, John (aka Jack) | Sales |
| 2 | Browne, Arlene | Sales |
| 3 | Carruthers, Sandy | Sales |
| 4 | Jameson, Kim | President |
3NF-SQL Phones: 9 rows, Indexed on key [EmployeeID + Phone] (9 values) and on Employee ID (4 Values)
| Employee ID | Phone | Phone Type |
| 1 | 555-1111 | Work |
| 1 | 555-2222 | Home |
| 1 | 555-3333 | Fax |
| 2 | 555-4444 | Home |
| 2 | 555-5555 | Work |
| 3 | 555-6666 | Work |
| 3 | 555-7777 | Cellular |
| 3 | 555-8888 | Fax |
| 4 | 555-9999 | Work |
3NF-SQL Departments: 2 rows, Indexed on key [Department ID] (2 Values)
| Department ID | Supervising Employee |
| Sales | 3 |
| President | 4 |
3NF-SQL Projects: 2 rows; Indexed on key [Project ID] 2 Values
| Project ID | Managing Employee |
| Smith | 2 |
| Jones | 3 |
3NF-SQL Project:Employee: 6 rows, indexed on key [Project ID + Employee ID] (6 values), on Project ID (2 values), and on Employee ID (4 values)
| Project ID | Employee ID |
| Smith | 1 |
| Smith | 2 |
| Smith | 4 |
| Jones | 1 |
| Jones | 3 |
| Jones | 4 |
3NF-SQL Employee:Manager: 4 rows, indexed on key [Employee ID + Manager ID] (4 values), on Manager ID (3 values), and on Employee ID (4 values)
| Employee ID | Manager ID |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |