0

I have a such problem which I couldn't solve in a good manner. My solution needs too much time and loop uses catastrophically big memory. So I need a help.

I have 3 mysql tables.

Gorups

  • group_id
  • group_name

games

  • game_id
  • game_name

questions

  • question_id
  • game_id
  • question_name
  • question_text

question_groups

  • question_id
  • group_id
  • order

The problem is following.

The questions are asked in following manner. One after another. The problem is that I need to make a shufle funnction in php in such way, that I will assign each question to each group in that way, that for example the first question for each group will be unique, and no 2 or 3 groups will get the same question, the same for second, third, .....tenth question.

What is the most optimal solution for this?

Should I use somehow mysql function for that or php?

Improve this question
3
  • I don't understand this question... can you give a worked example to help explain it? Commented Jul 17, 2010 at 23:00
  • @Mark Byers If I understand it correctly, the table question_groups needs to be populated, based on entries in the questions table. I have no idea how group_id is supposed to be related. Commented Jul 17, 2010 at 23:05
  • As I told on my comment for the answer lower, the game is in such way that there is 10 rounds, for 10 groups with 10 questions. in each round the groups gets questions and the questions should be assigned in a way that each group gets the question that is not given to another group on the same round and is not already answered. And this table of sequense should be defined beforehand. we should have a table 10X10 , with numbers 1-10 and in each row and column we don't have duplicate numbers. Commented Jul 17, 2010 at 23:46

1 Answer 1

Reset to default
1

The easiest way to solve this issue is to define a unique constraint/index for the question_id column in QUESTIONS_GROUPS. This constrains the data so that the value can only occur once in the entire table - a user would receive a unique constraint error if they attempted to add a duplicate question_id value.

Here's the statement you'd need to use to define the constraint in MySQL:

CREATE UNIQUE INDEX question_idx BTREE ON QUESTION_GROUPS(question_id)

Edit:

If you need to support a question being asked only once per round, I'm assuming that the QUESTION_GROUPS.order column is what the round value is. Assuming that's correct, add the order column to the unique constraint/index to ensure unique pairs of values:

CREATE UNIQUE INDEX question_idx BTREE ON QUESTION_GROUPS(question_id, order)

If you made the group_id and question_id columns to be the composite primary key for the table, it won't work for your requirements because it would allow the question_id column value to be duplicated.

I would also define that the QUESTION_GROUPS.group_id not be allowed to be NULL, otherwise you could assign a question to a NULL (non-existent) group and would have to update the existing record or delete & recreate it with a valid group.

Reference:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

the problem is that the same question shouldnt be given on the first round to more than one group. game is with rounds 10 groups 10 rounds 10 questions. I understand that I can make composite index, the problem is the php side, How to generate right insert.....
@DR.GEWA: The right insert? Insert what you like, the unique constraint will tell you if you attempt to associate a question either more than once or more than once per round, depending on your business rules...
it's not only about the 2 times the same question to the same person, but also not to giving the same to 2 different persons.....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.