Hi, Steven and christopher, Thanks a lot for your help. I think i have to store the question number in table. my talbe questionbank store the questions related more than one course. every course has six question sets. so i use question set id and question number as primary key. There is one problem is every time i delete one question for example delete question 5, i have to update all the question numbers after question 5 in this question set.(for example if there is 10 question in this set, i delete question 5 i have to update question numbers for question 6,7 8 9,10 to 5,6,7,8,9) it will take some time. but i think this is for administrator so maybe speed is not very serious issue. Thanks steven for his recommendation for the transactional table i will take a look at it. This is great mailing list. Thanks again bin cai --- Steven Hajducko <[EMAIL PROTECTED]> wrote: > 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 > > > >
______________________________________________________________________ Find, Connect Date! http://personals.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
