Complaint database in a Sales Environment

[AlanKW]

I’ve been asked to build a database to track complaints from consumers about ability to find our product. So one complaint can involve x number of stores not having our product. We’d want to track the notal number of complaints as well as a complaint per-store.

Jane Smith calls and says that she cannot find Gizmos at MegaMall in Anytown or Bobs Warehouse in N. Anytown.
John Doe also cannot find Gizmos at Bobs Warehouse in N. AnyTown as well as at ToyPalace in W. Anytown.

So I’d want to regard this as 2 complaints, but when I went to MegaMall, Bobs Warehosue and ToyPalace and say that there were complaints about not having any Gizmos. So the Totals of the 2 reports would not be equal.

In setting up the database, I was thinking of storing the data into 2 tables
Tbl_Complaints & Tbl_ComplaintStores (theres another table that stores all stores)

Tbl_Complaint would have the information making the complaint as well as a unique identifier.
Tbl_ComplaintStores would store The StoreID and the unique identifier


So when I wanted to see how many complaints per month, I’d query Tbl_Complaint but when the reps were hitting the store, I’d go against Tbl_ComplaintStores with a one to many to Tbl_Complaint to say This many people couldn’t find Gizmos on this day or that.

Does this sound like it’d work?

 

[lespaul]

Sounds exactly right! Since you have a one to many relationship between complaints and stores (one complaint can be about many stores) you would create one complaint in the tblComplaints and many stores in tblComplaintStores. You may want to put the productid in tblComplaintStores (since a person may file one complaint on many products)

So to continue your example from above:

Jane Smith calls and says that she cannot find Gizmos or Gadgets at MegaMall in Anytown or Gizmos at Bobs Warehouse in N. Anytown.
John Doe also cannot find Gizmos at Bobs Warehouse in N. AnyTown as well as at ToyPalace in W. Anytown.

Now Jane has two products from one store and one product from the second store.

HTH

Leslie

 

[AlanKW]

Thank you. This is the way that I set it up after pondering and before your response and is working good - sans some un-anticipated wrinkles that may appear in a new thread.