SQL Data Model
Summary
This section describes the basic data model –how data is organized– of a company's SQL database. Each database contains one or various tables (also known as collections or property types). Each table represents a data type and is made up of columns and rows. Each row is an element or property in the table. The columns indicate how the data of each element is organized and related to other tables. Some tables also include dynamic columns for adding extra data.
Table name structure
We have structured table names in such a manner to make it visually easier to understand the content in your database. The following list shows the possible structures for table names:
- [model]
- [model]_subtype
- [model]_[model]
- [model]_[model]_subtype
- Model indicates the data type.
- Singular names are normally used. Plural names are used when there are various table relations.
- Examples are provided in the section below.
Data Types and Models:
channels
Table with all company channels
- Number: 1
- Naming: n/a
Columns
_id: varchar(24) not null primary key,
display: varchar(1000),
code: varchar(1000),
isactive: boolean,
modifiedat: timestamp,
modifiedat_unixms: bigint
channel_property_*
Channel Property Relations
- Number: equal to property types
- Naming: channel_property_[model] e.g., channel_property_products, channel_property_stores, channel_property_engines, etc.
Columns
property: varchar(24) not null,
channel: varchar(24) not null,
Primary Key
primary key (channel, property)
channel_user
Channel User Relations
- Number: 1
- Naming: n/a
Columns
channel: varchar(24) not null,
userid: varchar(24) not null,
Modifiers
primary key (channel, userid)
data_*
Short description
- Number: equal to the number of surveys
- Naming: data_responses, data_new_issue, etc.
Columns
cot_user: varchar(24),
channel: varchar(24),
createdat: timestamp,
startdate: timestamp,
enddate: timestamp,
modifiedat: timestamp,
modifiedat_unixms: bigint,
__DYNAMIC__: __DYNAMIC__,
uuid: varchar(1000) not null primary key
NOTE: DYNAMIC columns depend on SURVEY models.
properties_*
Properties Table
- Number: Equal to Collections or Property Types
- Naming: propertiesclients, properties*
Columns
_id: varchar(24) not null primary key,
display: varchar(1000),
code: varchar(1000),
isactive: boolean,
propertytype: varchar(1000),
modifiedat: timestamp,
modifiedat_unixms: bigint
__DYNAMIC__: __DYNAMIC__
NOTE: DYNAMIC Columns are schema nodes, i.e., a sub-table or collection that stores extra data. They can be manually created in the Admin as additional fields of a collection.
sessions
Sessions Table
- Number: 1
- Naming: n/a
Columns
_id: varchar(24) not null primary key,
userid: varchar(24),
device: varchar(1000),
createdat: timestamp,
endedat: timestamp,
modifiedat_unixms: bigint
survey_list
Survey List Relations
- Number: 1
- Naming: n/a
Columns
uuid: varchar(1000),
identifier: varchar(1000),
value: varchar(1000)
survey_property_*
Survey Property Relations
- Number: Same as property types
- Naming: survey_property_clients, survey_property_colors, survey_property_cells, etc.
Columns
uuid: varchar(1000),
property: varchar(24),
identifier: varchar(1000)
survey_survey
Survey Survey Relations
- Number: 1
- Naming: n/a
Columns
parent: varchar(1000) not null,
child: varchar(1000) not null,
surveyid: varchar(24),
code: varchar(1000),
constraint: ss_pkey
Modifiers
primary key (parent, child)
survey_user
Survey User Relations
- Number: 1
- Naming: n/a
Columns
uuid: varchar(1000),
identifier: varchar(1000),
userid: varchar(24)
Modifiers
(uuid, identifier)
subproperties
Properties Properties Parent Child Relations
- Number: 1
- Naming: n/a
Columns
parent: varchar(24) not null
parent: varchar(24) not null
child: varchar(24) not null
parent_propertytype: varchar(1000)
child_propertytype: varchar(1000)
task_answer
Task Answer Relations
- Number: 1
- Naming: n/a
Columns
task_id: varchar(24) not null,
answer_uuid: varchar(1000) not null,
constraint: ta_pkey
Modifiers
primary key (task_id, answer_uuid)
tasklogs
Task Log Table
- Number: 1
- Naming: n/a
Columns
_id: varchar(24) not null primary key,
task: varchar(24),
taskgroup: varchar(24),
currentstate: varchar(24),
currentstatemachine: varchar(24),
changedby: varchar(24),
createdat: timestamp,
createdat_unixms: bigint
task_*
Tasks Table
- Number: Same as Workflows
- Naming: task_engineering, task_issues, task_bugs, task_machines, etc.
Columns
_id: varchar(24) not null primary key,
name: varchar(1000),
assignee: varchar(24),
smstate: varchar(24),
status: varchar(24),
asset: varchar(24),
status1: varchar(24),
status2: varchar(24),
status3: varchar(24),
status4: varchar(24),
status5: varchar(24),
createdat: timestamp,
modifiedat: timestamp,
modifiedat_unixms: bigint,
owner: varchar(1000),
startdate: timestamp,
enddate: timestamp,
resolutiondate: timestamp,
createdby: varchar(24),
smstatemachine: varchar(24),
taskgroup: varchar(24),
survey: varchar(24),
channeltype: varchar(100),
indentation: integer,
weight: bigint,
relativeweight: bigint,
projectcode: varchar(24),
parent: varchar(24),
isactive: boolean,
channel: varchar(24),
info: varchar(1000),
closedat: timestamp,
serial: integer
users
User table
- Number: 1
- Naming: n/a
Columns
_id: varchar(24) not null primary key,
email: varchar(1000),
name: varchar(1000),
isactive: boolean,
jobtitle: varchar(100),
phone: varchar(100),
createdat: timestamp,
modifiedat: timestamp,
modifiedat_unixms: bigint,
NOTE: Extra user elements can be added through properties tables.