Database design issue - trying to avoid circular reference

I have been at this for a day and a bit now, trying to figure out how to best model the database (MySQL) for an app I'm developing for a friend who owns a bakery. The assumptions are as follows:

• many (external) Bakers produce many Products
• BakersProducts is updated fortnightly by certain staff who either call bakers for their product prices, or the bakers fax through their pricelist themselves, which the staff then update via a front-end UI.
• the manager should be able to generate an order based on the products that she anticipates having.

So the front-end UI must be able to allow the manager to purely choose the products she would like in the order, and then present her with a list of Bakers to choose from for each product in the order.

In other words, Orders_has_Products should also include a reference to BakersProducts.bpID. I'm sure though that if I do this, then I would create a circular reference (of sort) to Products.

Im sure I've gone about this the wrong way, and would really apprec开发者_Go百科iate anyone's advice as to how I can restructure my design to acccommodate the chosen Product Price - ie. to include BakersProducts.bpID.

Thank you!

---

This is not a circular reference, since

• Order_has_products references Products
• Order_has_products references BakersProducts
• BakersProducts references Products

a circular reference would be if, for example,

• Order_has_products references Products
• Products references BakersProducts
• BakersProducts references Order_has_products

Aside from that, circular references are relatively normal in a database (i.e. Employees table with a manager field, where the manager is herself an employee is a one table circular reference)

What your design has is a simple redundancy, because one product is referenced twice in the Order_has_products table - once directly from the Products table, and once via the related BakersProducts record. There is posibility for getting out of sync, but, since you stated that the business rule is that the product is chosen before the baker, it's quite all right.

I would include the productID even if it was the other way around, because a little denormalization can go a long way when speeding up queries, because otherwise you would have to scan the BakersProducts table, even for simle questions, like, 'Did we have any bagels on wednesday?'

---

I think the mix-up is from a business process standpoint: you're getting requisitions mixed up with orders.

A requisition has a list of products needed without necessarily specifying the supplier of each, whereas an order is directed at a specific supplier, for specific price look-up codes (what bpID seems to represent). One requisition may spawn multiple orders if it is split across multiple suppliers, and even a single product may have its order split across multiple suppliers, perhaps due to vendor volume limits or locality of delivery.

You may want to provide a view of a requisition that shows the order line item(s) generated from each requisition line item, but that is a user interface concern.

---

one way of solving this is simply to eliminate the Products table, and move productName into the BakersProducts table.

This would essentially only work if you do not expect the bakers to carry the same product, if the products are unique to the bakers.

If you do expect the bakers to carry the same product, then you may want to leave the separate Products table, but instead of having Order_has_Products.Products_productID, I would change it to Order_has_Products.bpID. If/when you need to access the productName (or other product related metadata that may go in that table) you could just do a join between BakersProducts and Products.