Comments: via email or post to discussion forum so others can see: https://groups.google.com/forum/#!forum/alex-glaros-data-framework
Alex Glaros - October 9, 2019
Engagement Squared's data model nests data tables in a new way that expands the relational concept.
First, every entity has a common data type built for it; the table is named "super_object". This enables all entities to be placed as "super_objects" in roles with any other entity. Example: an organization and a person can be added as stakeholders in a project. Their super_object.id represents them in the same table column.
Second, the super_objects are in relationships and roles. The super_objects are in a subject/predicate/object model and can be either a subject or object. When the super_objects are placed in different role records, the status of the role becomes the only place status can be expressed. Status is only allowed within the context of a role. Time is only allowed to exist in the context of the status.
This enables complex systems to be built using very little code and effort, in the most intelligent way, and organizes the entire data environment.
Only a few tables needed to create any type of business application.
Most of the tables are nested within each other in the order below and are all one-to-many in the relational sense, with the parent on top.
The design captures the inner workings of what makes a process a process. The capability arises from the special way that "status" is only within role_member and "time" is only within status.
Tables are implemented in a regular relational database like Postgres. Primary automatic surrogate key of integer type is assumed for all tables.
SUPER_OBJECT - Table to standardize object types. It's simply a number-id that represents each object regardless of what type it is. It's a super-type of all object types. Its fields are super_object_ID (just a surrogate key) and object_type. Object type examples are person, submarine, project, license-number, cat, university, etc. Any entity. Example of super_object record: 12223, cat. The cat record would have a foreign key pointing back to its super_object record. Now everything has its own super_object record that can interact with anything else and represent it in a relationship with all other things. The result is reduced programming barriers and improved interoperability.
| super_object | |
|---|---|
| id | primary key, integer |
| object_type_fk | e.g., dog, refrigerator, ocean, person, planet |
| object_display_name | Fido |
CUSTOM TABLE for each object - Every object type gets its own table, like: car, project, house. (For illustrative simplicity custom table info is omitted in documentation further down the page.) Example:
| cat | |
|---|---|
| super_object_fk | super_object_fk is the foreign key to table super_object. This is how any type of thing has its ID standardized to fit into any role or role_member. |
| cat_first_name | kitty-cat |
| cat_fur_color | gray |
RELATIONSHIP_TYPE - This table contains the main categories of all the domains in business and government. Examples: Enterprise Architecture, Customer Service, Organizational Structure.
| relationship_type | |
|---|---|
| relationship_type_name | Enterprise Architecture, Customer Service, Organizational Structure, Family, Personal Connections |
ROLE_TYPE is a child table of above relationship_type table, example: "Security"
| role_type | |
|---|---|
| relationship_type_fk | |
| role_type_name | Security, Employee, Spouse |
OBJECT_SUPER_OBJECT - 1:M object for a db.role record below. Keeps track of what object is the object of a role, which can have 1:M objects. For example Susan could be the role object for role "Assignee" for incident#1, and George could be the role object for role "Assignee" for incident#2.
| object_super_object | |
|---|---|
| super_object_fk | super_object_fk is the foreign key for the object that is the object of a role, e.g., Susan or George. |
ROLE - Child table of role_type table. In this table we have a "object_super_object" field that contains the entity playing the object of the role. This allows for many objects playing the same role, and the same object playing many roles. For example, during movie production, if the role was named "hero", then a movie title would become the object of the role by populating the object_super_object field. This allows different movies to have a role "hero". Movies populating the object-role could be: Titanic, E.T., The Wizard of Oz, Star Wars, The Lord of the Rings, Avengers-Endgame.
| role | |
|---|---|
| role_type_fk | actor |
| object_super_object_fk | Avengers |
| role_name | hero |
ROLE_MEMBER - Child of "role" table above. In this table we have a "member" field that contains role members, in our example above, "hero" actors, e.g., Tom Hanks, Shahrukh Khan, Emma Stone, Chris Hemsworth, Scarlett Johansson, Robert Downey Jr. The parent foreign key is object_super_object_fk. Role_member can't point directly to the role record because we don't know which object is the role's object, so object_super_object_fk ties it together.
| role_member | |
|---|---|
| object_super_object_fk | |
| member_super_object_fk | Tom Hanks |
ROLE_MEMBER_STATUS_INSTANCE - Child table of "role_member" table. This table contains 1:M status of each role_member record. Examples of multiple statuses for the same role_member in a professional-licensing application could be (1) "Valid" as a primary status and (2) "On-probation" as a secondary status. For a movie production, it would show if an actor was shooting a scene. This is the only table in the entire system where status can be expressed.
| role_member_status_instance | |
|---|---|
| role_member_fk | |
| status | Filming ("Status" is simplified, actually uses a status-validation-table so real field name would be status_fk). |
If the super_object is an object, it's in the role table. If it's a subject, it's in the role_member table.
Note that there is the "object" of a predicate, meaning similar to "target". "Object" here has that meaning, but also used in another meaning like "thing". To clarify this potential confusion, think of of subject/object/predicate as "subject/thing/predicate" while our super_object can be either a subject or thing in the subject/thing/predicate relationship.
Again for clarification, "object" has two meanings.
ROLE_MEMBER_STATUS_TIME- Child table of status table above. Time of the status. Fields can be "start-date" and "end-date", but client requirements can create any type of time, such as "payment-due-date". Example, Tom Hanks was filmed Jan 3, 2019 from 1pm to 3pm, and Jan 4, 2019 from 9am to 12pm. This is the only table in the entire system that can express time. Nowhere else can time be added.
| role_member_status_time | |
|---|---|
| role_member_status_instance_fk | |
| from_date | Client controlled. Any kind of start date, format |
| through_date |
The above tables handle almost everything to build any type of application. We just need to add a custom table for each object type. Using our example above, we have a "person" table to hold person information such as actor's name and ID, and a "movie" table to contain the movie names.
So in the above example, every actor and movie get their own individual super_object_ID that can interoperability be assigned as role objects or role members.
That's it. Now any type of application can be quickly, and perfectly created with these few files. Its simplicity is a little deceiving so observe how it handles diverse problems below.
The only minor complexity is the object_super_object table because if the role member pointed directly to the role, it wouldn't know what was the object was. Object_super_object tells the role member what the role's object is.
For illustrative simplicity, super_object and custom table info is omitted. Assume there is a primary surrogate key for each table. Example:
Table Name - e.g. person_table |
| primary_surrogate_key - 1234 |
| name - Bob |
RELATIONSHIP_TYPE: family
ROLE_TYPE: spouse
ROLE: Husband, object_super_object_fk = family#3 (Note: object_super_object_fk is a field in role table)
ROLE_MEMBER:
Fred
ROLE: Wife, object_super_object_fk = family#3
ROLE_MEMBER:
Cecilia
ROLE_TYPE: Child
ROLE: Son, object_super_object_fk = family#3
ROLE_MEMBER:
Thomas, STATUS: at-college START-TIME January 1, 2010 END-TIME June 26, 2014
Remember that (1) family#3 (2) Fred (3) Cecilia (4) Thomas have a standardizing record type "super_object" that lets them play any role:object_super_object_fk or role_member:member_super_object_fk
Notice that it can easily handle polygamy/polyandry and many other difficult concepts that traditional databases cannot without extensive effort.
RELATIONSHIP_TYPE: Enterprise Architecture
ROLE_TYPE: Security
ROLE: Requirements, object_super_object_fk = Medical-professions-licensing-application
ROLE_MEMBER:
SQL-injection-prevention, STATUS: completed START-TIME January 1, 2014 END-TIME June 26, 2014
cross-script-attack-prevention, STATUS: pending START-TIME: January 1, 2014 END-TIME:
Above can easily handle security for many types of projects. Users just populate different projects into the object_super_object_fk field of the role record.
Deep complexity is handled by adding roles for relevant data, e.g., security-followup-schedule.
RELATIONSHIP_TYPE: Organization
ROLE_TYPE: External business roles
ROLE: General-Partner, object_super_object_fk = Household-Mover-License#3933
ROLE_MEMBER:
Sue, STATUS: valid START-TIME January 1, 1988 END-TIME June 26, 2018
Connie, STATUS: pending-live-scan START-TIME:
RELATIONSHIP_TYPE: Customer Service
ROLE_TYPE: Priority Management
ROLE: Staff, object_super_object_fk = Mary
ROLE_MEMBER:
Medical-professions-licensing-application, STATUS: #1-Priority START-TIME January 1, 2014 END-TIME
Applicant-tracking-system, STATUS: #2-Priority START-TIME: January 1, 2014 END-TIME: December 12, 2014
A key to business success is tracking client priorities. This example envisions client-controlled data that they enter themselves. Data can be sorted so that all people whose priority is the Medical-professions-licensing-application can be displayed in a report. Notice the end-time for priority#2; this allows reports to filter it out.
No programming at all was required to create priority tracking. Already in the system were (1) staff and (2) Medical-professions-licensing-application. Users themselves could create this function. Also, users define their own status codes.
The same programming code is used for all examples because they all use the same tables.
RELATIONSHIP_TYPE: Event
ROLE_TYPE: Meeting
ROLE: Meeting Segment, object_super_object_fk = January 12th Podiatrist Meeting
ROLE_MEMBER:
segment-A, STATUS: On-time START-TIME January 1, 2014 9 a.m. END-TIME January 1, 2014 10 a.m.
segment-B, STATUS: canceled
RELATIONSHIP_TYPE: Location
ROLE_TYPE: Event
ROLE: Meeting Segment Location, object_super_object_fk = Marriot-Sacramento-Hotel
ROLE_MEMBER:
segment-A, STATUS: Clean-the-premises START-TIME January 1, 2014 6 a.m. END-TIME January 1, 2014 7 a.m.
segment-A, STATUS: Deliver-food START-TIME January 1, 2014 6 a.m. END-TIME January 1, 2014 6:15 a.m.
segment-A, STATUS: Totally available and ready START-TIME January 1, 2014 7 a.m.
We needed 2 relationships to handle the meeting time and location. Cleaning and food delivery were simplified. Normally they would be in the "requirements" role.
Observe how the location relationship above centralizes all addresses across the enterprise and specifies what role they play The same location can play different roles, e.g., (1) a historical speech location (2) crime-scene, (3) meeting venue, etc. All the roles any entity plays are easily available.
Time, enterprise wide, is centralized within "status". The result is significant improvement in data analytics. If a data server mysteriously goes down at 1 am every morning, a time comparison would match the time that janitorial staff come through the door, revealing that they improperly power off the server in order to vacuum the room, then power it back on. This integrative design provides new features without additional coding required.
RELATIONSHIP_TYPE: Workflow
ROLE_TYPE: On-boarding-new-employee
ROLE: Requirements, object_super_object_fk = Fred
ROLE_MEMBER:
install-telephone, STATUS: Completed START-TIME January 1, 2014 7 a.m. END-TIME January 1, 2014 10 a.m.
install-computer, STATUS: Completed START-TIME January 1, 2014 9 a.m. END-TIME January 1, 2014 10 a.m.
RELATIONSHIP_TYPE: Duties
ROLE_TYPE: Assignment
ROLE: Assignment-area, object_super_object_fk = Fred's-telephone-requirement-above
ROLE_MEMBER:
Two relationships were needed, workflow and duties. Notice that role_member "install-telephone" in Workflow relationship becomes an object in Bob's Duties relationship assignments (Fred's-telephone-requirement) as a object_super_object_fk in the role record.
Using two roles to represent a process is not wasted space. It is the most efficient way to separate and clarify processes. Any other method would obfuscate roles and add complexity that eventually becomes unsustainable.
This method, when used to replace most business applications, creates data organization across the whole organization.
It requires multiple relationships to build out the entire organization but the method re-uses the same few tables that any programmer or data modeler can configure. The result will be enterprise-wide interoperability.
RELATIONSHIP_TYPE: Fee
ROLE_TYPE: Licensing
ROLE: Requirements, object_super_object_fk = Nurse-License#124
ROLE_MEMBER:
fee_type#1, STATUS: Due DUE-TIME: January 1, 2014 7 a.m. FINAL-PAYMENT-DEADLINE: January 1, 2015 10 a.m.
lic-fee-type#2, STATUS: Paid PAID-TIME January 1, 2014 9 a.m.
Users configure the names of roles, role_members, statuses, and status-times, e.g., see above where user created PAID-TIME time of the status. Users can create any type of time.
RELATIONSHIP_TYPE: Enterprise Architecture
ROLE_TYPE: Capability-Management
ROLE: Competency-Gaps, object_super_object_fk = Medical-professions-licensing-application
ROLE_MEMBER:
competency-opportunity#5 STATUS: active START-TIME: January 1, 2014 END-TIME:
RELATIONSHIP_TYPE: Connection
ROLE_TYPE: Personal Connection
ROLE: connected, object_super_object_fk = relationship#10
ROLE_MEMBER:
Tom STATUS: active START-TIME: January 1, 2014 END-TIME:
Sally STATUS: active START-TIME: January 1, 2014 END-TIME:
Below we have George who is blocked by Sally, but our method easily tracks that Tom blocked George only from Jan 1 to Jan 8. Notice that hardly any new programming code is needed to create a full-service personal-connection blocking system. The historical record tracking is comprehensive, agile, and automatic.
RELATIONSHIP_TYPE: Connection
ROLE_TYPE: Block
ROLE: blocked, object_super_object_fk = George
ROLE_MEMBER:
Tom STATUS: active START-TIME: January 1, 2014 END-TIME: January 8, 2014
Tom STATUS: canceled START-TIME: January 8, 2014 END-TIME:
Sally STATUS: active START-TIME: December 1, 2014 END-TIME:
Below we create subject matter expert lookup with no programming whatsoever. What is significant is that the pieces already there (1) various computer systems, act as data that connect to (2) staff, also already in the system.
RELATIONSHIP_TYPE: Organization
ROLE_TYPE: Informal Roles
ROLE: SME, object_super_object_fk = Schools-Information-Automated-Links-System
ROLE_MEMBER:
Dave STATUS: highest-expert START-TIME: January 1, 2014 END-TIME: January 8, 2014
Sally STATUS: moderate-expert START-TIME: December 1, 2014 END-TIME:
The objects, in this case the one that require expertise, "Schools-Information-Automated-Links-System", have all of their related roles in one convenient integrated database. Not only experts on that IT system, but everything else about that IT system, such as project due dates, requirements, managers, etc. Recall priority-management example that was built with no code where one of the objects was the medical-licensing system. Recall the enterprise-architecture and requirements roles for the medical-licensing system above. We can add the medical-licensing system to our SME model here and so far the following info is available regarding medical-system: EA, priorities, subject-matter-experts, requirements. This demonstrates how integration makes data smarter. Insight is gained into all aspects of the organization.
RELATIONSHIP_TYPE: Enforcement
ROLE_TYPE: Licensing
ROLE: Citation, object_super_object_fk = Nurse-License#87
ROLE_MEMBER:
Citation#929 STATUS: active START-TIME: January 1, 2014 END-TIME:
Citation#371 STATUS: paid START-TIME: January 1, 2014 END-TIME: January 8, 2014
Analyze what a relational programmer would do to create a meeting calendar application. They would create "meeting-segment", then in the same record add two time fields, "start-time" and "end-time". Disadvantages: There would be no time-comparisons available in a centralized table. Time would be distributed all over the enterprise in disconnected places without historical tracking. Also, clients could not automatically create new status codes and innovative time types.
Then in the same record, the programmer would add a "meeting-location" field. Yes, it would reference a validation table of addresses, but value would be lost as addresses in other tables would be in unconnected silos. With this new framework, all locations are in one table that provides meaning though the roles assigned to them. In summary, the new method will save many of lines of otherwise wasted programming code that create disintegrated data systems. Programming time is wasted building functions that really should be roles with members whose actions are defined by status. Our method quickly creates more useful software systems automatically for most applications and completely organizes information in the clearest, most useful manner across the enterprise.
Status only exists within role member
A status must only be added or changed within the context of a role with members. Otherwise, the value of status is diminished.
Time only exists within status
Time cannot be added or changed without being associated with a status. If a status changes, its time table states when. Time anywhere else has diminished meaning and capability.