Thread: ERD Help
09-07-15, 03:41 #1Registered User
- Join Date
- Sep 2015
I'm trying to make an ERD for a juice bar with the following requirements (for a university project):
- The juice bar sells a range of mixed juices, along with non-juice items, such as bottles of water
- The non-juice items are all fixed-size items with a fixed price (eg bottle of water = 2 dollars)
- The mixed juices come in three different sizes (400mL, 500mL, or 600mL)
- The mixed juices are made by combining various pure juices and other ingredients (such as coconut water) in any combination that the customer wants (eg 20% apple juice, 40% grape juice, 40% orange juice)
- Each of the ingredients has a unit price (per mL) and the price of the mixed juice is the sum of the costs of the ingredients.
- A customer places an order, which might have any number of different mixed juices and any number of non-juice items.
(There are other requirements, but they have no relation to the part I'm stuck on)
I'm having a problem figuring out how to track mixed juices placed in an order. Non-juice items could just have a product code, and that could be used to identify it in an order (which I've seen on many ERD's I've seen on the internet), but mixed juices are decided on the spot when the customer orders it, so there aren't pre-assigned product codes. I've been struggling to think of a way that they could be identified in an order, but haven't been able to come up with anything. I've thought of combining non-juices and mixed juices into a single product table, and giving them an item number within a particular order, but kept on having difficulties with the fact that mixed-juices and non-juices were too different (mixed-juices had varying sizes, costs, had many ingredients, while non-juices were fixed size, fixed price, no ingredients). I also thought of just giving each juice a juice number, and having it auto-increment for every juice made, and having that serve as a pseudo product code, but that seemed messy.
A sample ERD of what I have so far is here. I know that as it is currently, it wouldn't work, but that's as far as I've been able to progress. I know that the relationship/participation constraints (one-to-many, many-to-many etc.) aren't all correct, but I've never used software to make an ERD and was in a hurry when making it.
After close to a week on this one part of the ERD, I'm at a loss, so I'm just wondering if anyone here can maybe give me a hint as to something I'm missing, or not understanding correctly, or a different way of thinking about it.
09-07-15, 11:27 #2Resident Curmudgeon
- Join Date
- Feb 2004
- In front of the computer
Look for a one to many solution that allows many ingredients (FK and ml_dispensed) for a given serving.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.