Relationship Logic--One to Many or Many to Many

[chuckcou]

I have 3 current table--

Store, Store Reviews, Store Questions

The relationship between Store and Store Review is one to many--Each Store can have Many Reviews. :

But I can not figure out-Store Reviews and Store Questions and need some help.

[color:green]1st--Each Store Review can have many Store Questions

BUT

Each Store Question can be used in many Store Reviews(I think) :

[color:orange]Data Example--Store question might be---

[color:green]What was your overall experience in the Store?

That can be used for multiple store reviews, right?

But a specific question might only be related to a few stores

[color:green]What was your rating of fly fishing equipment?

This would only be realted to stores with Fishing equipment.

[color:red]So, is the example many to many? If so, then I would need a join table right?As you can see Many to Many are not my strong point. :

Thanks for you feedback chuck

Edited August 12, 2008 by Guest

[bcooney]

Yes, you need a join table, maybe two.

Can each review be used for many stores or is the review (set of questions) unique to a store?

Tables:

Stores

StoreReview (join btw reviews and stores)

ReviewsLibrary (just really a group of questions)

RevQuest (join btw Reviews and Questions)

QuestionLibrary

So, on a Review form, you'd have a portal to RevQuest related by ReviewID. You need a way to add a QuestionID to the RevQuest record, thus assemblying the Questions from your library of Questions that are used in this Review.

Now you want to assign this Review to a Store. I've assumed this Review can be assigned to many Stores. So, on a Store record, have a portal to StoreReview. By adding a ReviewID to this portal record, you've assigned this review to this store. I'd have some dates in that portal record, too, Date_Reviewed, for example.

I'm not sure that this is clear. Basically, you have a library of Reviews that you assign to Stores, and a library of Questions that you assign to Reviews.

What about Answers? Seems like RevQuest needs a table of child answer records related by RevQuestID. So, on a RevQuest record, have a portal to Answers to record all the feedback for this question for this review at this store.

[chuckcou]

maybe 2 :qwery: Huh--

Can each review be used for many stores or is the review (set of questions) unique to a store?

[color:green]Not sure how this is possible--EAch review would be unique to that store. Two store can have the same question though. eg.

Rate the lighting in the store? This is a question that can be posed for many stores. But each store lighting is unique to that store.

Rate the location of this store? This is unique to this store

Kinda of lost in my own loop--

Tables:

Stores OK, [color:red]I understandStoreReview (join btw reviews and stores) [color:red]OK, I understandReviewsLibrary (just really a group of questions)[color:red]OK I get this

RevQuest (join btw Reviews and Questions) [color:red]LoST

QuestionLibrary [color:red]LoST

I can see this now--

Store {storeidkey}-->

Store Review{storereviewidkey, storeidsecondarykey}-->

StoreReview&StoreQuestionJoinTABLE{storereviewidSecondaryKey, Storequestionsecondarykey}<--->

Storequestion{Storequestionkey}

Wouldn't the review be with the question.

Hmm, I think I am confused

[bcooney]

I'll probably have time to do up a demo tonight.

I was thinking that a review (which is just a collection of questions, not the question) could be used for more than one store or/and the same store more than once (different times).

The answers are unique per store, not the questions.

Since one review has many questions, you need a table for the review that holds the questions for that review "RevQuest". Picture a screen that has Review 100 with a portal to the questions included in this review. That portal would show records from RevQuest.

QuestionLibrary is all the questions you may ask/include in any review. You select from this and assign questions to a review. RevQuest has _kF_ReviewID and _kF_QuestionID.

[comment]

Seems very similar to:

http://fmforums.com/forum/showtopic.php?tid/183639/

[chuckcou]

Got the quesiton library down.

"RevQuest" I am still a bit shakey on this part. I will re read and check out the link below. Maybe that will help.

[bcooney]

Here's a bit of a demo to take apart.

Maybe it's a bit over-engineered for your needs. If you need to run the same review again, then having a Review Library will help you add reviews quickly and consistently.

The rest is interface. By storing ids of the original template and questionID, you can perhaps report across reviews by question.

Survey.fp7.zip

[chuckcou]

bcooney:

It has taken me a little time to figure out you demo. Very complex to me, but understandable.

I have a question. If I understand correctly--You assign a Title to a group of questions. Then you use this(__kp_review_id) for the store review.

This would work great if you were to keep using the same questions, but in my case I think I will be reusing less question. Thus, most stores will have different question.

That being the case wouldn't I want ot delete the Review Table, and the Review Questions(Join Table) and link Question Library directly to Store Review. Thus Store Review will have--

__kp_storeReview_id

_kfstore_id

_kf_question_id

dateofreview

By doing this I would have to choose each question for each store. Thus not allowing me to group the question by a review title.

[bcooney]

There is no right way, and I did say my approach may be over-engineered.

I went with the idea that you'd want to keep a set of questions to reuse. They are linked together by their ReviewLibID and the ReviewLib is given a title.

You could simply create a new review for a store without copying an existing RevLib record, and add questions from your question library directly to a RevQuest table. You would not have a library of pre-configured Reviews. So, everytime you make a new review, you'd have to add all the questions.* If you don't foresee "re-using" a review, then it's not a problem.

Don't link QuestionLib directly, because you need a "copy" of the question in RevQuest**. If linked directly, and you change the question text in QuestionLibrary, you've just changed your RevQuest. It's like a price in an invoice. You always copy the price when you add the line item, rather than have a "hot" link to the product's price. That way, if you change the price in the product record, you're not changing historical invoice line items.

So, keep QuestLib a source of questions, but not "hot-linked" to RevQuest. I do put the QuestID in RevQuest, just because it might come in handy. You could then have a portal on a QuestLib record that shows all the RevQuest records with that ID.

Also, did you say that you may run the same review twice for a store? That'll make having a ReviewLibrary useful.

_____

*(you can do both: have a set of question come in from the Review Template and still be able to add/delete from the Question Library)

**or don't allow editing of a QuestLib record if that ID exists in RevQuest.

Edited August 14, 2008 by Guest

[chuckcou]

Hmm. I am kind of following you, but I am confused about what tables to remove.

So, everytime you make a new review, you'd have to add all the questions.* If you don't foresee "re-using" a review, then it's not a problem.

This is correct. This is what I want to do. This give me tha ability to pick and choose the quesitons.

Don't link QuestionLib directly, because you need a "copy" of the question in RevQuest**. If linked directly, and you change the question text in QuestionLibrary, you've just changed your RevQuest. It's like a price in an invoice.

Yes, that make total sense. You don't want older invoice to be updated.

So, keep QuestLib a source of questions, but not "hot-linked" to RevQuest. I do put the QuestID in RevQuest, just because it might come in handy. You could then have a portal on a QuestLib record that shows all the RevQuest records with that ID.

I can't get this to work correctly. What am I missing. This is probably where I am lost at the moment.

Also, did you say that you may run the same review twice for a store? That'll make having a ReviewLibrary useful.

Yes, this is possible and will happen. For the most part if I can add questions one at time that would be acceptalbe.

For some reason I can't get your existing version to allow me to add the record 1 at a time from the library. It might be I am not fully sure what table to delete.

Chuck