InstaGov/schema/question

About

• Purpose: implements questions
• Notes:
  • I originally had a "WhenExpires" field as a way of making a decision at a specific time, but I decided that this belongs in a separate table (something like "decision point"). Timetable functionality feels "tacked-on", and putting it in a separate table allows keeping that feature from becoming entangled with basic voting. We might want to be able to implement, say, more than one decision-point per question.
  • Not sure if we'll actually need Pos/Neg_Text; it may be just a frill.
  • 2011-05-02 Makes no sense to have Pos/Neg Text; that's an Answer thing. (See InstaGov/SMW/questions for obsolete notes.)
  • 2012-12-28 Making the structure dependent on wiki pages increases platform-dependency and CPU overhead. Keep the table, make wiki page optional (it should default, at page creation time, to a calculated name which can be updated if the page is moved). We will probably need a log of edits to Answers. In fact, let's do a general log.

History

• 2012-12-28 Removing *_Text; adding Summary, WhoCreated
• 2013-01-03 changed WhoCreated and WhoEdited from string to integer
• 2013-01-07 Added UNIQUE KEY for Summary
• 2013-07-14 Revising to use PostFerret
  • Removing fields duplicated in parent tables: Summary, Page (post), WhenCreated, WhoCreated, WhenEdited, WhoEdited, QtyEdits (atom)
  • Alternatively, we could have eliminated this table altogether and used the post table instead by adding a "class" field (to distinguish between "questions" and other types of posts), since this record now adds no other information, but we may later want to add some fields specific to the "question" class... and so far none of the other post-derivatives are capable of being this stripped-down, so the "class" field would be redundant for them.

SQL

<mysql>CREATE TABLE `question` (

  `ID_Post`     INT              NOT NULL COMMENT "post.ID for content",
  PRIMARY KEY(`ID_Post`)
)
ENGINE = MYISAM;</mysql>

Obsolete

<mysql>CREATE TABLE `question` (

  `ID` INT  NOT NULL AUTO_INCREMENT,
  `Summary`     VARCHAR(255)     NOT NULL COMMENT "short description of question",
  `Page`        VARCHAR(127) DEFAULT NULL COMMENT "optional wiki page with more information",
  `WhenCreated` DATETIME         NOT NULL COMMENT "when this question was entered",
  `WhoCreated`  INT              NOT NULL COMMENT "ID of user who added the question",
  `WhenEdited`  DATETIME     DEFAULT NULL COMMENT "when this question was last edited",
  `WhoEdited`   INT          DEFAULT NULL COMMENT "ID of user who last edited the question",
  `QtyEdits`    INT          DEFAULT NULL COMMENT "number of times this question has been edited",
  PRIMARY KEY(`ID`),
  UNIQUE KEY `content` (`Summary`)
)
ENGINE = MYISAM;</mysql>