Support:CSAT Implementation Strategy
Nelson: I'm not really in favor of creating new db tables, since there is a lot of functionality in TikiWiki already. So I recommend an implementation using polls as the main data collection mechanism, with trackers as the means to track and report on aggregated information.
Polling system
The advantage of using polls is that architecturally, each poll can be linked to any object. Polls themselves are stored in tiki_polls, and the linking to objects is done in tiki_poll_objects.
Right now though, the implementation for multiple polls per object is not complete, since there is no way to decide which of the multiple polls to show on which object, and how to refer to the multiple polls in terms of defining layout for them (show what where). Given the current db schema though, it should be possible to refactor the code to make it completely extensible to any number of polls <-> object relationships -- if we just create a tiki_poll_groups (and tiki_poll_groups_items) to store the poll groups (each pollId is unique to a single poll - and each group refers to polls of a certain "question").
The options for each poll are stored in tiki_poll_options. Right now, there is only a "title" but no "value" for poll options. Perhaps a "value" field can be added.
The votes themselves are accumulated in tiki_poll_votes, and in addition it is possible to keep track of what users have voted before through tiki_user_votings (this depends on the user being logged in though, unless we extend this to track non-logged in voters by email)
The main disadvantage of polls is that they are not designed to get free-form information and are really good only for collecting "multiple choice responses". If we really need to collect a free form response (e.g. for live chat), then it will be inserted into a tracker through a custom form.
mysql> desc tiki_polls;
+--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+----------------+ | pollId | int(8) | NO | PRI | NULL | auto_increment | title | varchar(200) | YES | | NULL | | votes | int(8) | YES | | NULL | | active | char(1) | YES | | NULL | | publishDate | int(14) | YES | | NULL | | voteConsiderationSpan | int(4) | NO | | 60 | +--------------------+--------------+------+-----+---------+----------------+
mysql> desc tiki_poll_objects;
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | catObjectId | int(11) | NO | PRI | 0 | | | pollId | int(11) | NO | PRI | 0 | | | title | varchar(255) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+
mysql> desc tiki_poll_options;
+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | pollId | int(8) | NO | | 0 | | | optionId | int(8) | NO | PRI | NULL | auto_increment | | title | varchar(200) | YES | | NULL | | | position | int(4) | NO | | 0 | | | votes | int(8) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+
mysql> desc tiki_poll_votes;
+-----------+---------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+------------+-------+ | optionId | int(8) | NO | PRI | 0 | | | voteDate | date | NO | PRI | 0000-00-00 | | | voteCount | int(10) | NO | | 0 | | +-----------+---------+------+-----+------------+-------+
mysql> desc tiki_user_votings;
+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | user | varchar(200) | NO | PRI | NULL | | | id | varchar(255) | NO | PRI | NULL | | | optionId | int(10) | NO | | 0 | | +----------+--------------+------+-----+---------+-------+
proposed tiki_poll_groups;
+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | pollGroupId | int(8) | NO | PRI | 0 | | | pollGroupName| varchar(200) | YES | | NULL | | | title | varchar(200) | YES | | NULL | | | position | int(4) | NO | | 0 | | +--------------+--------------+------+-----+---------+-------+
proposed tiki_poll_groups_items;
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | pollGroupId | int(8) | NO | PRI | 0 | | | pollId | int(8) | NO | | 0 | | +-------------+--------------+------+-----+---------+-------+
Use of trackers for higher level tracking
Each article/forum post and so on will have a corresponding tracker entry.
We will start off with an initial set of fields we need, and because trackers do not need DB changes to add fields, we can add new fields later if they are required.
The tracker will track:
*where the data was gathered (kb/forum/chat), *what the problem was (reference to kb article, forum post, or live chat log) *who helped the user (applies to forum/chat only) *aggregate CSAT for the item
In order to get:
* Get average overall CSAT score * Get average per-component CSAT score (kb/forum/chat) * Say "Contributor X has an average CSAT of xx %," which will be useful for the karma/scoring system
We will write a "summary statistics package for trackers" that will do this kind of calculations.
Another advantage of trackers is the possibility of more easily embedding tracker information into wiki pages that can be displayed for review (without having to do actual code to generate these reports).
Bug 452829 should then be more about tracker design than actual MySQL DB table design.
If the number of items become too large, then we can create a "tracker archive" feature - basically store only the high access info on the main tracker tables, but have less accessed data on another set of tracker tables.
Karma system
Users already can have a score linked to them, in users_users table
This score is incremented when an event that is stored in tiki_score is triggered. We can just extend with with custom events and custom triggers to add score.
The exact event that led to the score is logged in tiki_users_score.
mysql> desc tiki_score;
+------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | event | varchar(40) | NO | PRI | NULL | | | score | int(11) | NO | | 0 | | | expiration | int(11) | NO | | 0 | | +------------+-------------+------+-----+---------+-------+
mysql> desc tiki_users_score;
+----------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------+------+-----+-------------------+-------+ | user | char(40) | NO | PRI | NULL | | | event_id | char(40) | NO | PRI | NULL | | | expire | int(14) | NO | | 0 | | | tstamp | timestamp | YES | | CURRENT_TIMESTAMP | | +----------+-----------+------+-----+-------------------+-------+
mysql> desc users_users;
+---------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+----------------+ | userId | int(8) | NO | PRI | NULL | auto_increment | | email | varchar(200) | YES | | NULL | | | login | varchar(200) | NO | MUL | NULL | | | password | varchar(30) | YES | | NULL | | | provpass | varchar(30) | YES | | NULL | | | default_group | varchar(255) | YES | | NULL | | | lastLogin | int(14) | YES | | NULL | | | currentLogin | int(14) | YES | | NULL | | | registrationDate | int(14) | YES | MUL | NULL | | | challenge | varchar(32) | YES | | NULL | | | pass_confirm | int(14) | YES | | NULL | | | email_confirm | int(14) | YES | | NULL | | | hash | varchar(34) | YES | | NULL | | | created | int(14) | YES | | NULL | | | avatarName | varchar(80) | YES | | NULL | | | avatarSize | int(14) | YES | | NULL | | | avatarFileType | varchar(250) | YES | | NULL | | | avatarData | longblob | YES | | NULL | | | avatarLibName | varchar(200) | YES | | NULL | | | avatarType | char(1) | YES | | NULL | | | score | int(11) | NO | MUL | 0 | | | unsuccessful_logins | int(14) | YES | | 0 | | | valid | varchar(32) | YES | | NULL | | | openid_url | varchar(255) | YES | MUL | NULL | | +---------------------+--------------+------+-----+---------+----------------+
