An obscure body in the SK system. The inhabitants call it Earth
Posts
7,957
Re: database design question
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.
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.
Declan
Don't forget to mark your Thread as resolved.
Take a moment to rate posts that you think are helpful
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.
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,957
Re: database design question
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.
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.
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?
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,957
Re: database design question
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)
Reply With Quote
