A simple database version control

If you have a table which stores questi开发者_JS百科ons and answers, for example using an extra column called something such as Parent. So if a question doesn't have a value in parent, it's a question and if it does, its a reply, so how would you do version control for this?

---

For a well-developed example of how to keep past revisions of text, you could look at the MediaWiki database layout. This is the database layout used by the MediaWiki software, which many wikis (notably Wikipedia) run on. Specifically, the page, revision, and text tables are the primary tables involved with keeping track of historic changes.

---

Have one table for questions, and a separate one for answers:

Here's an example in SQLite

The questions table should contain the text of the question and an id field to join on:

sqlite3
sqlite> CREATE TABLE questions(id, question);
sqlite> INSERT INTO questions VALUES(1,"What is your name");
sqlite> INSERT INTO questions VALUES(2,"Phone Number");
sqlite> INSERT INTO questions VALUES(3,"name of dog")
sqlite> SELECT * FROM questions;
1|What is your name
2|Phone Number

The answers should contain a revision number or date, the text of the answer, a field to indicate which user is asked the question (if necessary), and the id of the question to which it is an answer.

sqlite> CREATE TABLE answers(question,userid,revision,answer);
sqlite> INSERT INTO answers VALUES(1,100,1,"Alex");
sqlite> INSERT INTO answers VALUES(2,100,1,"1234");
sqlite> INSERT INTO answers VALUES(2,100,2,"5678");
sqlite> SELECT * from answers;
1|100|1|Alex
2|100|1|1234
2|100|2|5678

Now query on the two databases, making sure that: only the most recent revision is selected (GROUP and MAX), and unanswered questions are included (LEFT JOIN)

sqlite> SELECT questions.question, answers.answer, userid, max(revision) 
                FROM questions LEFT JOIN answers ON questions.id = answers.question
                GROUP BY questions.id, userid;
What is your name|Alex|100|1
Phone Number|5678|100|2
name of dog|||

You can create some other queries to list past answers for a given question/user, and to list unanswered questions by user.