InstaGov/schema/answer

About

 * Purpose: implements s
 * Rules: Any may have zero or more answers. New answers can be added at any time.
 * Notes:
 * This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "" table.

History

 * 2013-01-07
 * Added "Summary" field, which I had removed earlier when I was still thinking of letting this be heavily dependent on MW, and who/when fields.
 * Added UNIQUE KEY "content" to prevent duplicate answers for the same question.
 * 2013-07-21 Revising to use ; many redundant fields removed. Also removed Pos_Text and Neg_Text fields for now.
 * The *_Text fields were intended as optional overrides to the values in, but the latter has not yet been implemented; I think I may have decided at some point that it wasn't necessary.

SQL
CREATE TABLE `answer` (  `ID_Post`     INT          NOT NULL COMMENT "post.ID for content",   `ID_Question` INT          NOT NULL COMMENT "question.ID (actually atom.ID) for which this is an answer",   PRIMARY KEY(`ID_Post`) ) ENGINE = MYISAM;

Obsolete
CREATE TABLE `answer` (  `ID`          INT          NOT NULL AUTO_INCREMENT,   `ID_Question` INT          NOT NULL COMMENT "question.ID for which this is an answer",   `Summary`  VARCHAR(255)    NOT NULL COMMENT "short description of answer",   `Page`     VARCHAR(127)             COMMENT "URI of optional site page giving more details",   `Pos_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'yes' end of the scale (overrides question default)",   `Neg_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'no' end of the scale (overrides question default)",   `WhenCreated` DATETIME     NOT NULL COMMENT "when this records was entered",   `WhoCreated`  INT          NOT NULL COMMENT "ID of user who added the record",   `WhenEdited`  DATETIME DEFAULT NULL COMMENT "when this question was last edited",   `WhoEdited`   INT      DEFAULT NULL COMMENT "ID of user who last edited the record", `QtyEdits`   INT      DEFAULT NULL COMMENT "number of times this record has been edited", PRIMARY KEY(`ID`), UNIQUE KEY `content` (`ID_Question`,`Summary`) ) ENGINE = MYISAM; For migrating data from old schema to new (some fields omitted): INSERT INTO atom (Class,WhenCreated,WhoCreated,WhenEdited,QtyEdits) SELECT "an",WhenCreated,WhoCreated,WhenEdited,QtyEdits FROM OLD_answer;

INSERT INTO post (ID_Atom,ID_Owner,Summary,Body) SELECT atom.ID,NULL,Summary,Body FROM OLD_answer AS a LEFT JOIN atom ON a.WhenCreated=atom.WhenCreated;

INSERT INTO answer (ID_Post,ID_Question) SELECT ID_Atom,ID_Question FROM OLD_answer AS a LEFT JOIN post ON a.Summary=post.Summary;