stuck on proper table relationship
I am getting closer (I believe) to finishing all my table and relationships setup, but I am stuck on how to get rid of a many to many relationship. Any general advice would be very helpful and apreciated!
I hope this explanation helps:
An estimate is unique by a customer id and an estimate id. Estimates, however, can have duplicate parts.
estimate id's can look like: E10020, E10020-1, E10020-2, E10021, E10022 etc.
customer ID's look like: EVPT, MTYF, SDRT (basically 4 letter codes)
Quotes are then created and sent out to vendors to quote (or "bid") on.
I've been trying to group the quote items by estimate id & customer id, which I hope would allow me to have one quote display all the estimate parts associated with that vendor in one simple quote.
The tricky part is that one or all parts of an estimate can be sent to one or many different vendors. I am trying to figure out how to group all or one part of an estimate into one quote for one vendor, for each vendor I want to send a quote to.
I have the following tables:
The problem is, when I try to relate records in quote item to one quote- it becomes a many to many relationship. I attached a picture of the relationship setup, and would be glad to provide further explanation to anyone who may be able to help.
*Just ignore the purchase order and invoice part of things for now, I will use the same approach for those once I figure out this quote part.
Any advice is very much apreciated.