Engagement Squared's data model does some amazing things.

  1. Any entity can be associated with any other entity in the world for creating powerful collaborative relationships.

  2. Every role for every entity is accessable for deep understanding of all aspects of an organization

SuperObject

The top-level entity is SuperObject. It has added a higher level of abstraction that encompasses almost all entities including suggestions, organizations, projects, and people. The only things it doesn't represent are things like messages and relationships.

SuperObject enables association and connecting of anything. E.g., a citizen suggestion may recommend a project that ties previously unconnected agencies or connects people, machines, projects, and different sections of the same organization together.

SuperObject's subtypes are (a) party (b) other components (see ObjectType table).

Party has two subtypes: person and organization.(see PartyType table). The two Party sub-types follow standard, modern data modeling practice.

Components are anything else besides a party and have their own custom data model, for example, projects, suggestions, addresses, etc.

It is extra programming work to maintain a superObject record for every entity, however it works well to enable cross-category interoperability. Also, all entities have some attributes in common, so this is where those items are stored. It makes it easy to have only one app handle things like assigning URLs, creating and editing comments, and deleting records. For example, there is only one comment table. It is works with any SuperObject record to enable comments attached to any object (regardless of record type!), anywhere in the program. The same few lines of code are re-used throughout the system.

Roles and Relationships

  • SuperObjects represent all entities.
  • Roles and Relationships tables tie the SuperObjects together and represent how they are related.

This allows enterprise-wide connection of everything, plus analysis into all relationships from any perspective.

That is what makes Engagement Squared different. Everything is represented as an object, and the Roles and Relationships tables allow citizens and government to connect everything across bureaucratic boundaries into innovative solutions that promote collaboration and break down traditional silo thinking.

Roles and Relationships tables handle a lot:

  • Personal connections between people
  • Define permissions to access data and authorizations to allow people to do things
  • Connect suggestions to organizations (Link suggestion to one or many organizations)
  • Organizational relationships such as employees of an organization
  • SMEs (subject matter expert) for systems
  • Interoperability opportunities such as APIs, and other data integration relationships
  • Project partners
  • All relationships within Engagement Squared

Role relationship table structure

Roles and relations data design is a powerful model that can be adopted by both private industry and government as a foundation for strong, flexible, and scalable IT applications of any type.

db.RelationshipType

This is the only relationship table and a parent table of RoleType table. It doesn't do much except act as a kind of categorization table for role types. It is great as a roadmap to roles that give insight into how your organization works. Examples:

  • Knowledge - this relationship categorizes SMES (subject matter experts) role type.
  • Interoperabilty - categorizes APIs, data integration role types.
  • Authorization - categorizes authorization for all types of role types.

db.RoleType

RoleType is like a lookup table for auth_group (role) table below. Example: (a) RelationshipType=Knowledge (b) RoleType=SME. RoleType is foreign key to auth_group below.

db.auth_group

This is the "role" table. It is a sub-type of RoleType. Has two main fields: roleType and superObjectID.

  • "roleType" is a FK (foreign key) from RoleType table.
  • "superObjectID" is the object that is acted on by the role.

For example, a role type could be "Authorizes web content that requires recurring approval for each new content item". The superObjectID could be the "The state of New York main website". The object that is acted on is a real object in the SuperObject table.

db.auth_membership

Finally, the whole picture comes into focus with auth_membership table. It has two main fields (1) user_id, which is a person, and (2) group_id, which is a foreign key to table auth_group. Simply put, it represents all of the people (user_id) that belong to a specific auth_group role.

Example:

  • db.RelationshipType: "Marriage"
  • db.RoleType: "Husband"
  • db.auth_group (role): (a) "Husband of Mary". (b) The other field in this record would be superObjectID value for Mary.
  • db.auth_membership: "Tom"

In db.auth_group, superObjectID is the object of the role as in Subject/Predicate/Object. For example: relationshipType:Marriage, roleType:husband/role: husband of Mary, superObject: Mary, then in auth_membership table, member of this role/object combination is Tom. Result is "Tom is husband of Mary".

Subject: Tom
Predicate: Husband
Object: Mary

Another example:

  • db.RelationshipType: "Knowledge"
  • db.RoleType: "SME" (subject matter expert)
  • db.auth_group (role): (a) "SME for Mexico's National Inventory System" (b) Other field in this record is superObjectId for the inventory system.
  • db.auth_membership: "Laura, Juan, Alexjandro, Maria, Jose, Antonio, Felipe, Teresa"

Roles and Relationship tables are a beautiful jewel that sit within the heart of Engagement Squared. They enable innovation, flexibility, and scaling. Anyone can use this data model to create the core for an enterprise system for anything, a social network, municipal government, licensing system, work flow, ticketing system, etc. Adding a good party data model (with person and organization sub-types) and well designed address and contact data design would start any system off in the right direction.

Personal/social connections

There are four db.RoleType personal connection types that have db.RoleType.relationshipType of "Personal-Connection" (belong to super-type db.RelationshipType of "Personal-Connection"): (1) Connection Initiation (2) Connect (3) Deny (4) Block. Two people can have a relationship defined by only one of the four role types above.

Two people cannot have more than one db.RoleType record with db.RoleType.relationshipType == 'Personal-Connection'. Each person will have these four db.RoleType records to define relationships with other people who are (1) aleady connected to them (2) have a pending connect request (3) who they have denied connection (4) who they have blocked.

To block someone, put their user_id in that other person's related-to-db.Role db.auth_membership blocked record. To connect with someone, put their user_id in that other person's db.auth_membership "connect" record. Same with denied and pending connections.

A duplicate method of keeping track of personal connection status exists to enable very fast searches. It keeps minimal data. There are two tables: db.BlockedConnection and db.VerifiedConnection. The reason the redundant Role table method exists is to have no exceptions to providing insight into every role within an organization. Otherwise, the simpler, faster method would be sufficient to handle social connections.

How authorizations work with role and relationship tables

This is basically a programming function. Under the "Authorization" category of Relationship records, the developer creates a RoleType record that triggers programmatic action. Programming code has to be written to execute the authorization.

Example. db.MeetingCategory classifies types of meetings for an organization. Government policy may require that posting meeting notices so public can see meeting dates and times, requires authorization. Meeting notices have to be approved before being displayed on public-facing servers.

An admin creates a db.RoleType record and names it "Requires Repeating Website Content Authorization". Then in table db.auth_group, admin assigns this RoleType to the db.MeetingCategory record using its foreign key superObjectID. Finally, in db.auth_membership, admin adds the people allowed to authorize each meeting. Code had to be written in controller to (1) insert authorization required data records whenever a new meeting is posted (2) display pending authorizations to people that approve them.

Engagement Squared has features that allow organizations to actually display their web content on Engagement Squared (or as content that appears on their site via html Iframe). In this case, programming goes one step further and has created features actually prevent or allow the content to be displayed via the authorization process.

Taxonomy table

Taxonomy table has a taxonomy type called: “Most Fundamental Categorization” which also makes them different things, categorizes all components. It redundantly has a category called “IT system”, and handles all components in the universe in basic categories: Examples: Suggestion, IT system, Business Process, Policy, Data, Manufactured Object, Property

Taxonomy database only has 3 tables. It “stacks” up columns so that the whole world of column types can fit in it. “IT Skills” for example, don’t have a separate column for “years of experience in PHP”, instead, in the data column, there are data values such as “more than 10 years PHP programming experience”, and that is associated with a person.

The way that geopolitical and taxonomy-type geopolitical tables work is that there is a join that brings in table GeoPoliticalDivision. GeoPoliticalName and OrganizationGovernmentGeoPoliticalDivisionIntersection to take advantage of geoPoliticalDivisionLevelID so that any country can find high, medium, or lower geopolitical software. Examples include financial systems for cities and counties. See these for example: search_opportunites_at_the_geo_political_division_level(): query = ((db.GeoPoliticalDivision.geoPoliticalName == db.TaxonomyData.taxonomyDataID) & (db.TaxonomyData.id ==db.TaxonomyDetail.taxonomyDataID))

Geo-Political data design

GovOrgJurisdiction table ties a specific entity, e.g., "Sacramento County Fire Dept", with a jurisdictional type, e.g., "US County"

Field "govOrgType" identifies the entity as either a "Geo-Political Unit" or a "Non Geo Unit". Geo-political means bounded geographicaly, like, city or state. Non-geo means geography is not relevant, such as with the "Department of Motor Vehicles", which can be located anywhere within the state.

Within table "Organization", field "EntireLegalEntity" defines an org as a whole legal entity or a sub-division such as the "Printing Division of the Dept. of Motor Vehicles".