For a school Food Delivery application I am developing, I’m facing some problems during the ER model design stage. I have to fulfil many requirements but my main issues I’m having are the following:
- Each order’s food items must be from a single restaurant, and each order is assigned to a delivery rider who will collect and deliver the ordered items to the customer.
I’ve learnt about ternary relationships and my current design doesn’t seem to be enforcing the constraint in (A). I’m thinking if a ternary constraint connecting Restaurant to “Ordered In” will be a better design but as I’m not familiar with refining schemas, I don’t know if it’s a good design decision.
Each full-time rider maintains a monthly work schedule (MWS) while each part-time rider maintains a weekly work schedule (WWS).
In the FDS application, a week is defined to be a duration consisting of seven consecutive days, and a month is defined to be a duration consisting of four consecutive weeks.
Each WWS specifies the hour intervals that the rider is available for work each day.
For example, a rider’s WWS might specify the following work hours for six work days: for Monday to Thursday, the rider works for the hours 10am to 1pm, 4pm to 6pm, and 7pm to 10pm; and for Saturday and Sunday, the rider works for the hours 10am to 1pm and 5pm to 8pm.
What would be a good way to differentiate between a part-time weekly work schedule and full-time monthly work schedule, which is a duplicate of 4 identical weekly work schedules, the exact wording of my requirements are all shown in (B).
I follow my notation rather similarly to those quoted in R. Ramakrishnan & J. Gehrke, Introduction to database design, Database Management Systems, chapter 2. McGraw Hill, third edition, 2003. Do feel free to ask for more clarification