Article feedback/Version 5/Technical Design Schema
- 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*/;
