database design question

I am having difficulty designing a database, it seems straightforward enough, but i'm having issues, any help would be appreciated.

The basic concepts are:
company
promotion
quiz
question
customer

every company has more than one promotion
every promotion can only have 0 or one quiz
every quiz can have many questions.

The problem i have is how to relate customers (and their answers) to the quiz/questions.

A customer may only play a promotions quiz once.
(the start and end time of when the customer layed the quiz also need to be recorded)

many customers can play a promotions quiz

If I'm understanding you correctly you want a 'Customer_Answer' table which would act as a linking entity between customer and question. The Customer_Answer table should a record containing a question number, the customer number and the answer they gave. You already know for each question what quiz it belongs to so there's no need to link to that.

Thanks for the reply funkydexter.

I understand how your solution would work, but how/where would i log the start and end times for the customer. (basically i want to be able to say, this customer took 10 minutes to complete the quiz).

I guess it wouldn't be correct to log this in the customeranswer table, as that is per answer, rather than the whole quiz.

I think you might want a little different setup. I would use a table between customer and quiz. That would allow you to record a start and stop time for the quiz and create a unquie key constraint on the table using the quiz PK and the customer PK. That would allow you to prevent a customer from taking a quiz more then once.

You need a Customer_Quiz table that has the Customer_ID and Quiz_ID as a combined PK (this will prevent a customer from taking a quiz twice.)
In this table you can store the start and end times for the quiz.

I think you might want a little different setup. I would use a table between customer and quiz. That would allow you to record a start and stop time for the quiz and create a unquie key constraint on the table using the quiz PK and the customer PK. That would allow you to prevent a customer from taking a quiz more then once. Then a table that would hold the question and answers for that quiz customer combination.

Gary
great minds think alike (and I guess us idiots do too) :wave:

Yep, as others have said, if you're going to have start and end times you're going to have to connect it to quiz as well with a Customer_Quiz table. I think you'll actually want this in addition to Customer_Answer table because you're still going to want to store the answers each customer gave.

Hmmm, ER(Entity-Relationship) Diagrams should show the idea of the peeps well sagey. :)

thanks to all for your help so far.
I have attached a png of an er diagram from sql server, is this the basics of what you were describing?

No need to link the tbl_CustomerAnswer to tbl Customer, just make the answer table relate to the question table, which in turn relates to the test table, and through the CustomerTest table you can establish, which test, which question and what the answer is.

Cheers for the reply bilbo.

Not sure if what you are suggesting is correct. if there is no direct relationship between a customer and his answers how do you know which
answers are a customers?

That looks spot on to me.

As a handy hint when you're creating diagrams like that, try and lay it out so all the relationships point down (ie they have the key at the top). This will make them a little easier to read and, also, if you can't arrange them with the relationships pointing down it means you're created a circular refernce (which is very bad)

cheers funkydexter. i'll give it a go.