On Fri, 2002-04-12 at 13:52, Christopher Thompson wrote:
> You shouldn't be storing the questio number at all. You have denormalised
> your database and this is why you are experiencing this problem.
Maybe I don't understand bin's problem, but storing the question #
wouldn't seem to be de-normalizing. It would only matter on how he
plans on storing it.
It would seem to matter on a few things -
1) Is he planning on storing more than 1 exam?
2) Can an exam really have the same question #, twice?
If yes, then -
Are the questions split upon sections of the exam?
Are the questions the same, but have subsections? ( 1a, 1b ? )
>From the looks of it, he doesn't seem to want to store more than 1 exam
anyways. If he did, he could use a 2 field primary key of exam name and
question number. There just isn't enough information on what he wants
to do.
>
> Instead, store a unique identifier with each one (an 'id' field,
> autonumbering) and then, when you pull back all the questions, order it by
> this ID field. Your questions won't have numbers assigned to them but you
> can do that in whatever app uses the questions.
Why not use the question number? It's unique per exam. (Or should be?).
I can see the case of doing this would help in his problem of the first
and second query, but what if someone enters questions out of order? If
he deletes a question, then tries to re-insert it, it would end up being
the last question in the exam.
You might want to look into using a transactional table bin. Then, you
could BEGIN the change, then after your two queries are both completed,
commit the change. Therefore, if your computer crashed in the middle of
the query, the table would revert to the old form.
> Of course, the truth is that you _can_ do what you want just using SQL. But
> trust me, you don't want to. It would be at least O(n^2), I think, and would
> be a pain to write.
>
> On Friday 12 April 2002 2:38 pm, bin cai wrote:
> > Hello, verybody,
> > I am afraid to send my question again.
> >
> > i created a table in mysql database system to store
> > exam questions which has the following column fields:
> > questionnumber(integer),questionbody(String), answers
> > (String) and etc.
> > My problem is if i delete one row(question record) how
> > can iupdate the questionnumber in the easiest way
> > which
> > means mysql can do for me. Is it possible? or i have
> > to write a method to update questionnumber.
> > if In second case i got another worry. for example,
> > i wrote two querys,one is to delete this question
> > record another query is to update the
> > questionnumber,that is , all records whose
> > questionnumbers are greater than the questionnumber of
> > the deleted question should deducted by 1. if at the
> > time After the first query is executed successfully
> > and the second query is being executed, the computer
> > is crashed. the second query will be rolled back. so
> > the question is deleted but the questionnumber is not
> > updated. this will result in a mess.
> > If anybody can give me some hint i will appreicate
> > very much
> > Have a good weekend
> > bin
> >
> >
> > ______________________________________________________________________
> > Music, Movies, Sports, Games! http://entertainment.yahoo.ca
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]> Trouble
> > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
