SQL adding an int field, and incrementing other fields based on that number in the same column

Question

I am in need of some inspiration for SQL design thinking, I have just started to learn SQL. My knowledge is very limited, so please bear with me..

I have a Quiz application, and in SQL, I have a whole row, corresponding with each Quiz questions and answers. It is very basic, and not designed very well. But here goes.

Like so..

My requirement is to give the users the ability to add a question at any point in the quiz list..So for instance, if a user would like to Add a new question at number 2, the user would be able to click the Add question at position 2, which would add a new record into the database and refresh the page to see that the questions have re-ordered and numbered..

My application pulls back the data based on Category.. So for instance if a user wants to view the History category in my application. They click a button and they are presented with the list of titles, and the below buttons..

As you can see.. My table is just not geared up for this.. Can anybody give me some tips, on how i could achieve this within the DB.

Many thanks.

Answer 1

One possible solution is to create one table for the questions. and one for the categories.

CREATE TABLE question
             (id integer,
              question varchar(64),
              PRIMARY KEY (id));

CREATE TABLE category
             (id integer,
              name varchar(64),
              PRIMARY KEY (id));

And then a table that links questions to categories with an ordinal that designates the position of the question in the category. That also has the charm that a question can be in more than one category.

CREATE TABLE category_question
             (category integer,
              question integer,
              ordinal integer,
              PRIMARY KEY (category,
                           question),
              FOREIGN KEY (category)
                          REFERENCES category
                                     (id),
              FOREIGN KEY (question)
                          REFERENCES question
                                     (id));

And, as I already suggested, an extra table for the answers, also with an ordinal.

CREATE TABLE answer
             (id integer,
              question integer,
              answer varchar(64),
              ordinal integer,
              PRIMARY KEY (id),
              FOREIGN KEY (answer)
                          REFERENCES question
                                     (id));

Whenever a question (or answer) is moved, you can change the ordinals accordingly. For display purposes you can order by it.

Answer 2

You want a Linked List for the ids and external table for the category (sticky bit handled this already).

When inserting your new row used a stored proc that does something like this:

update QuizTable set position = position + 1 where position >= 2;
insert into QuizTable(position, question) values (2, "new question");

If your table requires concurrent updates then this will not work of course!! (as you could get locking if one user updates position 8 while you update position 2.

The only way to do this safely for a multi user system would be to implement next/prior columns (or put them in a separate database table). This option is a difficult task and you should probably avoid it.

Your insert stored procedure would look something like this:

declare @myNewId = 94;
declare @position2Id = 50;
insert into QuizTable(id, question)
values (@myNewId, "my new question");

declare @next, @prior
select @next = next, @prior = prior from QuizTable where id = @myNewId;

update QuizTable set next = @myNewId where id = prior;
update QuizTable set prior = @myNewId where id = @position2Id

Then your list will be made up of a dynamically calculated id and a self referenced table scan where id 1 = prior IS NULL id 2 = id1.next

Just found this link for you: Linked List in SQL