1

I'm not an expert in databases, so this is complex for me but maybe it's no for a lot of people. I have the following:

quiz: Table which contains some quizzes

project: Table which contains some projects

project_has_quiz: A many-many relationship table project-quiz

team: Table which contains some teams

event: Table which contains some events (a project plus a date and some other fields)

event_has_team: A many-many relationship table event-team

Now, I have to assign an order for the quizzes for each team in an event. For example:

Say you have 5 quizzes a, b, c, d, e.

You create a project projectA with quizzes a, b and c.

You have two teams: teamA and teamB

You create and event eventA with projectA and you relation it with teamA and teamB.

Now I have to put somewhere that eventA with teamA must have a quiz order a, b, c and eventA with teamB must have a quiz order c, a, b.

What is the best way to do this?

First idea I have is to build another table: event_has_team_has_quizOrder, with a column quizOrder having inserted through programming code a, b, c and c, a, b. But I don't think this is the most pure way to achieve this and that this surely have problems of denormalization.

Thank you very much.

Waiting for Dev...
  • 12,629
  • 5
  • 47
  • 57

1 Answers1

1

you could do

event_has_team_has_quizOrder

(event, team, quiz, sequence)


eventA, teamA, a, 1
eventA, teamA, b, 2
eventA, teamA, c, 3
eventA, teamB, c, 1
eventA, teamB, a, 2
eventA, teamB, b, 3
goat
  • 31,486
  • 7
  • 73
  • 96
  • But isn't there any better way? I mean, I would like `event_has_team_has_quizOrder` `quiz` column values to be, forced by the database, in the range defined by `quiz` column value in the project related in the event. In your answer, maybe the only one possible, I have to ensure with programming that `quiz` column never is `d` or `e`. Thank you. – Waiting for Dev... Dec 22 '11 at 22:54
  • Good question. There's no current table that you could make (event, quiz) a foreign key to. Hopefully someone more experienced comes along to suggest ways you could go aside from using check constraints/triggers. – goat Dec 23 '11 at 18:40
  • Thanks @chris. I'll wait and otherwise I'll set your answer as the correct one. – Waiting for Dev... Dec 23 '11 at 21:26
  • Even using check constraints/triggers I can't get what I want, because I'm using MySQL. In MySQL check contraints are parsed but [they have no effect](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-not-working) and, at least in MySQL, you can't throw an error using a trigger (that is what I would like to do), even if there is a [hack](http://stackoverflow.com/questions/24/throw-an-error-in-mysql-trigger) but I preffer to don't use it because I consider it's not a clean way. So I think I'll go on implementing the constraint with programming code. – Waiting for Dev... Dec 24 '11 at 12:11