Mobile Service Database Tables
The following is a list of tables added to the AusStage relational database to support the MobileService.
Please Note:
- Tables will be listed here only after they have been added to the relational database
- The table specification may differ from that found in the MobileServiceSpecification in response to issues discovered during implementation
- The name of the datatypes match those implemented by Oracle which may differ from other database platforms.
- Importantly no datatype is Oracle specific
- All new tables that are created specifically for the MobileService have the prefix mob_
Performances
Details of the individual performances that have sought feedback using the MobileService
Table Name
mob_performances
Columns
| Name | Description | Datatype | Key / Indexed |
| performance_id | Used as a primary key for the table | number | Primary |
| event_id | link this performance to the existing AusStage event table | number | FK (events) |
| start_date_time | the start of the period during which valid feedback can be received | date | |
| end_date_time | the end of the period during which valid feedback can be received | date | |
| question_id | the id of the question asked at this performance used to solicit feedback | number | FK (mob_questions) |
| hash_tag | the unique hash tag for this performance | varchar(40) | Unique |
| deprecated_hash_tag | a field to indicate that this hash tag has been deprecated | varchar(1) - default 'N' | |
Notes
- For simplicity during initial development each performance can only have on question associated with it. This can be expanded to multiple questions using an intermediary table that linked a performance to multiple questions
- The events table already exists in the AusStage database
- Date and time fields are entered and stored using Adelaide Local Time
- The Twitter API imposes a limit on the number of keywords (hashtags) that can be used with the streaming API. As performance specific identifiers do not have a date / time restriction it is necessary to have an alternate method of identifying hashtags that are no longer being used. This is why the deprecated_hash_tag field is used. If set to 'Y' the hash tag is considered to be deprecated and will not be used when connecting to the streaming API.
Organisations
This table stores information about organisations using the MobileService
Table Name
mob_organisations
Columns
| Name | Description | Datatype | Key / Indexed |
| organisation_id | Unique identifier of the organisation in AusStage | number | FK (organisionid in the organisation table) |
| twitter_hash_tag | Specify the organisation specific hash tag | varchar(64) | Yes (No duplicates) |
| mobile_phone_hash | Specify hashes of phones of organisation employees for filtering | varchar(64) | |
Notes
- Organisation specific hashtags are used to identify twitter messages that are of interest
- Mobile phone hashes are stored in order to filter out feedback that is sent from people associated with the organisation, for example employees testing the service
Questions
This table stores details of the questions that have been used to solicit feedback
Table Name
mob_questions
Columns
| Name | Description | Datatype | Key / Indexed |
| question_id | Unique identifier for questions | number | Primary |
| question | Text of the question | varchar(512) | |
| question_notes | Notes about the question used internally by AusStage | varchar(4000) | |
Feedback
This is a table used to store feedback sourced from Twitter, using the TwitterGatherer application as well as SMS messages. The table is designed to be extensible to support additional columns / content types as required.
Table Name
mob_feedback
Columns
| Name | Description | Datatype | Key / Indexed |
| feedback_id | Used as a primary key for the table | number | Primary |
| performance_id | Link this feedback to a performance | number | FK (mob_performance table) |
| question_id | Link this feedback to a specific question | number | FK (mob_questions) |
| source_type | Identify the source of feedback | number | FK (mob_source_types) |
| received_date_time | The date and time that the feedback was sent | date | |
| received_from | A cryptographic hash of the unique identifier of the user as defined by the source type | varchar(64) | yes |
| source_id | A cryptographic hash of the unique identifer for the piece of feedback in the source system | varchar(64) | yes |
| short_content | The content of the feedback | varchar(4000) | |
| long_content | The content of the feedback when longer than 4000 characters | clob | |
| content_uri | A URI for content stored outside the system | varchar(4000) | |
| public_display | Indicates if the feedback should be publicly displayed | char(1) | yes |
Notes
- The SHA-2 algorithm, specifically sha256, is used to compute cryptographic hashes
- Date and time fields are entered and stored using Adelaide Local Time.
- The public_display field by default is set to 'Y'
Source Types
This table stores information about the various source types that are supported by the MobileService
Table Name
mob_source_types
Columns
| Name | Description | Datatype | Key / Indexed |
| source_type | Unique identifier for the various sources | number | Primary |
| source_name | The name of the source | varchar(255) | |
| source_description | The description of the source | varchar(512) | |
Other Database Objects
Sequences
A sequence is an object in Oracle that is used to generate a number sequence. The following sequences are used to create a unique number to act as a primary key.
| Table | Column | Sequence |
| mob_performances | performance_id | mob_performance_id_seq |
| mob_questions | question_id | mob_question_id_seq |
| mob_feedback | feedback_id | mob_feedback_id_seq |
| mob_source_types | type_id | mob_source_type_id_seq |
Database Diagram
A copy of the database diagram is available for download.
