Table Relationships & Tags
This article shows these different relationships between tables:
Table relationships and tags
Relationship between the Activity table and the Customer list
Relationship between the activity table and the matrix table
Relationship between the matrix table and the list
Relationship between the activity table and the multiple choice table
Relationship between the activity table and the visit reports
How to fetch the Activity report / List ids from the webapp
Checking list Assignation (scope) in the webapp
Assignation tables
Relationship between the mobile users table and the items mobile users
Relationship between the teams table and the items teams
When creating queries that form different charts, relationships between tables in a database are established using various types of keys, such as primary keys and foreign keys.
These keys help define the relationships and maintain data integrity.
You can use JOIN clauses to combine data from multiple tables based on these relationships when writing SQL queries to retrieve data from related tables.
Find below the different tables, what they contain, and their relationship.
The activity table consist of data from the activity report while the customer list table has details of the customers.
The matrix table consist of all the information (questions) filled in the matrix question.Each matrix table is named as per the name of the matrix .E.g a_sdtfhyjkuipmh_branding_audit, this table will be for the matrix branding_audit.
The matrix table may consist questions that need to use the lists hence it will be linked to the list needed eg the sku,competitor products,assets etc.
The multiple choice table will consist of all the information from all the multiple choice questions in an activity.In this table, we have a column named _custom_field_tag which consist of all the tags for the different multiple choice questions,it can be used to filter the information for a specific question when developing queries eg
SELECT * FROM a_sdtfhyjkuipmh_multiple_choice where _custom_field_tag = '_visit_objectives'
This query will return all the entries for the multiple question on visit objective.
In the table we have the column _ object_id which contails all the different choices submitted from the different multiple choice questions.
The multiple choice table could also be linked to any list which the field pro user will be use to select the difference choices from.
The visit_reports table will store all the visits done on activities of the Report prerequisite Need visit or geo check-in or customer selection.
id |
Below is a video on where to fetch the ids or either the activity report or the lists from the webapp.
https://www.loom.com/embed/f1d385f6c1be4548bf665ad98065260f?sid=8c624687-2fa9-41ec-a5b4-816767a399c2
The lists could be set to have either of the below scopes in the webapp.Below is the meaning for each scope
Global - the items in the list are visible to everyone
Teams - items are accessible by more than 1 team. 1 item can be assigned to more than 1 team
Single team - 1 item in the list can only be assigned to 1 team.Different teams cannot have visibility to the same item
Mobile users - items are accessible by more than 1 mobile user. 1 item can be assigned to more than 1 mobile user
Single Mobile users - 1 item in the list can only be assigned to 1 Mobile user.Different Mobile users cannot have visibility to the same item.
Below is a video on where to check/edit the scope of the lists in the webapp
https://www.loom.com/embed/5243e9e89338496a90c9f45aa4925a0d?sid=ec932719-1efe-446b-9eea-aa29f2216127
We have two tables that store data on assignation depending on the scope
items_teams - This table contains information on which items have been assigned to each teams and also the list id of the items.The scope can be teams or single team.
items_mobile_users - This table contains information on which items have been assigned to each user and also the list id of the items.The scope can be mobile users or single mobile users
This relationship is useful if you need to get more details on the users who have been assigned the items from any list eg name, role etc The column list_id can be used to filter on the items assigned from a particular list.
e.g
SELECT * FROM items_mobile_users WHERE list_id = 'customer'
This relationship is useful if you need to get more details on theteams who have been assigned the items from any list eg name, region, etc The column list_id can be used to filter on the items assigned from a particular list.
Table relationships and tags
Relationship between the Activity table and the Customer list
Relationship between the activity table and the matrix table
Relationship between the matrix table and the list
Relationship between the activity table and the multiple choice table
Relationship between the activity table and the visit reports
How to fetch the Activity report / List ids from the webapp
Checking list Assignation (scope) in the webapp
Assignation tables
Relationship between the mobile users table and the items mobile users
Relationship between the teams table and the items teams
Table relationships and tags
When creating queries that form different charts, relationships between tables in a database are established using various types of keys, such as primary keys and foreign keys.
These keys help define the relationships and maintain data integrity.
You can use JOIN clauses to combine data from multiple tables based on these relationships when writing SQL queries to retrieve data from related tables.
Find below the different tables, what they contain, and their relationship.
Relationship between the Activity table and the Customer list
The activity table consist of data from the activity report while the customer list table has details of the customers.
| Activity table(a_activity id) eg a_sdtfhyjkuipmh | customer list (l_customer) |
|---|---|
| _ customer_ id | _id |
Relationship between the activity table and the matrix table
The matrix table consist of all the information (questions) filled in the matrix question.Each matrix table is named as per the name of the matrix .E.g a_sdtfhyjkuipmh_branding_audit, this table will be for the matrix branding_audit.
| Activity table(a_activity id) eg a_sdtfhyjkuipmh | Matrix table (a_sdtfhyjkuipmh_branding_audit |
|---|---|
| _id | report id |
Relationship between the matrix table and the list
The matrix table may consist questions that need to use the lists hence it will be linked to the list needed eg the sku,competitor products,assets etc.
| Matrix table ((a_activity_id_matrix name) eg a_sdtfhyjkuipmh_products | l+ list id (sku/competitors/assets etc) ) e.g l_sku,l_xvbcghtuom) |
|---|---|
| _ object_ id | _id |
Relationship between the activity table and the multiple choice table
The multiple choice table will consist of all the information from all the multiple choice questions in an activity.In this table, we have a column named _custom_field_tag which consist of all the tags for the different multiple choice questions,it can be used to filter the information for a specific question when developing queries eg
SELECT * FROM a_sdtfhyjkuipmh_multiple_choice where _custom_field_tag = '_visit_objectives'
This query will return all the entries for the multiple question on visit objective.
In the table we have the column _ object_id which contails all the different choices submitted from the different multiple choice questions.
The multiple choice table could also be linked to any list which the field pro user will be use to select the difference choices from.
| Activity table(a_activity_id) eg a_sdtfhyjkuipmh | a_sdtfhyjkuipmh_multiple_choice |
|---|---|
| _id | _ main_ table_record_id |
| a_sdtfhyjkuipmh_multiple_choice | list (sku/competitors/assets etc) ) e.g l_sku,l_xvbcghtuom) |
|---|---|
| _object_id | _id |
Relationship between the activity table and the visit reports
The visit_reports table will store all the visits done on activities of the Report prerequisite Need visit or geo check-in or customer selection.
| Activity table(a_activity_id) eg a_sdtfhyjkuipmh | visits_report |
|---|---|
| _visit_id | id |
id |
How to fetch the Activity report / List ids from the webapp
Below is a video on where to fetch the ids or either the activity report or the lists from the webapp.
https://www.loom.com/embed/f1d385f6c1be4548bf665ad98065260f?sid=8c624687-2fa9-41ec-a5b4-816767a399c2
Checking list Assignation (scope) in the webapp
The lists could be set to have either of the below scopes in the webapp.Below is the meaning for each scope
Global - the items in the list are visible to everyone
Teams - items are accessible by more than 1 team. 1 item can be assigned to more than 1 team
Single team - 1 item in the list can only be assigned to 1 team.Different teams cannot have visibility to the same item
Mobile users - items are accessible by more than 1 mobile user. 1 item can be assigned to more than 1 mobile user
Single Mobile users - 1 item in the list can only be assigned to 1 Mobile user.Different Mobile users cannot have visibility to the same item.
Below is a video on where to check/edit the scope of the lists in the webapp
https://www.loom.com/embed/5243e9e89338496a90c9f45aa4925a0d?sid=ec932719-1efe-446b-9eea-aa29f2216127
Assignation tables
We have two tables that store data on assignation depending on the scope
items_teams - This table contains information on which items have been assigned to each teams and also the list id of the items.The scope can be teams or single team.
items_mobile_users - This table contains information on which items have been assigned to each user and also the list id of the items.The scope can be mobile users or single mobile users
Relationship between the mobile users table and the items mobile users
This relationship is useful if you need to get more details on the users who have been assigned the items from any list eg name, role etc The column list_id can be used to filter on the items assigned from a particular list.
e.g
SELECT * FROM items_mobile_users WHERE list_id = 'customer'
| mobile_users | items_mobile_users |
|---|---|
| id | mobile_user_id |
Relationship between the teams table and the items teams
This relationship is useful if you need to get more details on theteams who have been assigned the items from any list eg name, region, etc The column list_id can be used to filter on the items assigned from a particular list.
| items_teams | teams |
|---|---|
| team_id | id |
Updated on: 13/11/2024
Thank you!