Table design - loooooong questionnaire

[Beemerang]

Hi All

Hypothetical example:

I have a long questionnaire (50 questions plus) for which the answers need to be captured into a table. The questionnaire deals with questions about restaurants (for which I already have a table in my database) e.g. meals offered, quality of food etc. The tricky part is that this questionnaire also contains details such as restaurant address and telephone number, name of owner etc. As the user enters the questionnaire data for each restaurant into the database, they may need to update the restaurant record details such as a new owner, address etc.

What would be the best way to implement this? Would a many to many relationship (junction table) be the only option?

Thanks
Beem

 

[Simon_MT]

The problem you have is dicriminating between owners. If the restuarant is taken over the quality changes that has an impact. If it improves then you do not want to penalise the current owners. The restaurant could even change name or serve different food.

Your juction table could be used to record the restaurant information at the time of the questionaire but link to a master record so that you can still retain historical information.

Simon

 

[Beemerang]

Thanks a lot for the response, Simon.

That makes sense. So I gather that a junction table is the only way to implement this?

Beem

 

[Simon_MT]

No. but need you just deal with the current ownership, style and reviews then dispense with the historical information afterall the "proof of the pudding is in the eating".

Simon

 

[Beemerang]

Out of curiosity, what would the other ways to implement this be?

Beem

 

[Simon_MT]

You keep the History:

Restaurants >> Restaurant Details
Restaurants >> Menus
Restaurants >> Reviews

You look at Restaturants and see Current and Past Owners, Menus and Reviews.

Simon

 

[Beemerang]

Thanks for taking the time to reply mate.
Beem