Article feedback/Version 5/Technical Design Schema

From MediaWiki.org
Jump to: navigation, search
See also: meta:Research:Article_feedback/Data

Stores feedback records: "user X submitted feedback on page Y, at time Z"

CREATE TABLE IF NOT EXISTS /*_*/aft_article_feedback (
 -- Row ID (primary key)
 af_id               integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 -- Foreign key to page.page_id
 af_page_id         integer unsigned NOT NULL,
 -- User Id (0 if anon), and ip address
 af_user_id         integer NOT NULL,
 af_user_ip         varchar(32) NULL,
 -- Unique token for anonymous users (to facilitate ratings from multiple users on the same IP)
 af_user_anon_token varbinary(32) NOT NULL DEFAULT ,
 -- Foreign key to revision.rev_id
 af_revision_id     integer unsigned NOT NULL,
 -- Which feedback widget the user was given. Default of 0 is "none".
 af_bucket_id       integer unsigned NOT NULL DEFAULT 0,
 -- Which CTA widget was displayed to the user. 0 is "none",
 -- Which would come up if they got the edit page CTA, and couldn't edit.
 af_cta_id          integer unsigned NOT NULL DEFAULT 0,
 -- Which link the user clicked on to get to the widget. Default of 0 is "none".
 af_link_id         integer unsigned NOT NULL DEFAULT 0,
 -- Creation timetamp
 af_created         binary(14) NOT NULL DEFAULT 
) /*$wgDBTableOptions*/; 
CREATE INDEX /*i*/af_page_user_token_id ON /*_*/aft_article_feedback (af_page_id, af_user_id, af_user_anon_token, af_id);
CREATE INDEX /*i*/af_revision_id ON /*_*/aft_article_feedback (af_revision_id);
-- Create an index on the article_feedback.af_timestamp field
CREATE INDEX /*i*/article_feedback_timestamp ON /*_*/aft_article_feedback (af_created);
CREATE INDEX /*i*/af_page_id ON /*_*/aft_article_feedback (af_page_id, af_created);
CREATE INDEX /*i*/af_page_feedback_id ON /*_*/aft_article_feedback (af_page_id, af_id);
CREATE INDEX /*i*/af_page_revision ON /*_*/aft_article_feedback (af_page_id, af_revision_id);

Allows for organizing fields into fieldsets, for reporting or rendering. A group is just a name and an ID.

CREATE TABLE IF NOT EXISTS /*_*/aft_article_field_group (
 afg_id   integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 afg_name varchar(255) NOT NULL UNIQUE
) /*$wgDBTableOptions*/;

Stores article fields, zero or more of which are used by each feedback widget We already used af_ as a prefix above, so this is afi_ instead

CREATE TABLE IF NOT EXISTS /*_*/aft_article_field (
 afi_id        integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 afi_name      varchar(255) NOT NULL,
 -- Allowed data types - relates directly to which aa_response_* field gets 
 -- set in aft_article_answer, and where we check for answers when fetching
 afi_data_type ENUM('text', 'boolean', 'rating', 'option_id'),
 -- FKey to article_field_groups.group_id
 afi_group_id  integer unsigned NULL,
 -- Which 'bucket' this field should be rendered in.
 afi_bucket_id integer unsigned NOT NULL
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/afi_data_type ON /*_*/aft_article_field (afi_data_type);

Stores options for multi-value feedback fields (ie, select boxes)

CREATE TABLE IF NOT EXISTS /*_*/aft_article_field_option (
 afo_option_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
 -- foreign key to aft_article_field.afi_id
 afo_field_id  integer unsigned NOT NULL,
 afo_name      varchar(255) NOT NULL
) /*$wgDBTableOptions*/;

Stores individual answers for each feedback record - for a given feedback record, what did the user answer for each individual question/input on the form with which they were presented.

CREATE TABLE IF NOT EXISTS /*_*/aft_article_answer (
 -- FKEY to article_feedback.af_id)
 aa_feedback_id        integer unsigned NOT NULL,
 -- FKEY to article_field.afi_id)
 aa_field_id           integer unsigned NOT NULL,
 -- Only one of these four columns will be non-null, based on the afi_data_type
 -- of the aa_field_id related to this record.
 aa_response_rating    integer NULL,
 aa_response_text      text NULL,
 aa_response_boolean   boolean NULL,
 -- FKey to article_field_options.afo_option_id)
 aa_response_option_id integer unsigned NULL,
 -- Only allow one answer per field per feedback ID.
 PRIMARY KEY (aa_feedback_id, aa_field_id)
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/aa_feedback_field_option ON /*_*/aft_article_answer (aa_feedback_id, aa_field_id, aa_response_option_id);

These next four are rollup tables used by the articlefeedback special page. The revision tables store per-revision numers, as we (in meetings with WMF) agreed that per-revision numbers could be useful in reporting, though they aren't currently used on the feedback page. The page-level ones only count back to wgArticleFeedbackv5RatingLifetime, so they're a rolling window.

There are tables for ratings and select (ratings includes booleans as well), because while the value of the rating/boolean is important (Rated 3/5), for selects we only want the count for each input, not the value of that input or the sum of the values (which will be numerical option_ids, not meaningful rating values). The queries were sufficiently different that we deemed multiple tables worthwhile.

CREATE TABLE IF NOT EXISTS /*_*/aft_article_feedback_ratings_rollup (
 arr_page_id   integer unsigned NOT NULL,
 arr_field_id  integer unsigned NOT NULL,
 arr_total     integer unsigned NOT NULL,
 arr_count     integer unsigned NOT NULL,
 PRIMARY KEY (arr_page_id, arr_field_id)
) /*$wgDBTableOptions*/;
CREATE TABLE IF NOT EXISTS /*_*/aft_article_revision_feedback_ratings_rollup (
 afrr_page_id      integer unsigned NOT NULL,
 afrr_revision_id  integer unsigned NOT NULL,
 afrr_field_id     integer unsigned NOT NULL,
 afrr_total        integer unsigned NOT NULL,
 afrr_count        integer unsigned NOT NULL,
 PRIMARY KEY (afrr_page_id, afrr_field_id, afrr_revision_id)
) /*$wgDBTableOptions*/;
CREATE TABLE IF NOT EXISTS /*_*/aft_article_feedback_select_rollup (
 afsr_page_id   integer unsigned NOT NULL,
 afsr_option_id integer unsigned NOT NULL,
 afsr_field_id  integer unsigned NOT NULL,
 afsr_total     integer unsigned NOT NULL,
 afsr_count     integer unsigned NOT NULL,
 PRIMARY KEY (afsr_page_id, afsr_option_id)
) /*$wgDBTableOptions*/;
CREATE TABLE IF NOT EXISTS /*_*/aft_article_revision_feedback_select_rollup (
 arfsr_page_id     integer unsigned NOT NULL,
 arfsr_revision_id integer unsigned NOT NULL,
 arfsr_option_id   integer unsigned NOT NULL,
 arfsr_field_id    integer unsigned NOT NULL,
 arfsr_total       integer unsigned NOT NULL,
 arfsr_count       integer unsigned NOT NULL,
 PRIMARY KEY (arfsr_page_id, arfsr_field_id, arfsr_revision_id, arfsr_option_id)
) /*$wgDBTableOptions*/;

Directly taken from AFTv4

CREATE TABLE IF NOT EXISTS /*_*/aft_article_feedback_properties (
 -- Keys to article_feedback.aa_id
 afp_feedback_id integer unsigned NOT NULL,
 -- Key/value pair - allow text or numerical metadata
 afp_key        varbinary(255) NOT NULL,
 afp_value_int  integer signed NOT NULL,
 afp_value_text varbinary(255) DEFAULT  NOT NULL,
 PRIMARY KEY (afp_feedback_id, afp_key)
) /*$wgDBTableOptions*/;
Personal tools
Namespaces

Variants
Actions
Navigation
Support
Download
Development
Communication
Print/export
Toolbox