I am new to databases and am trying to make an Entity Relationship Diagram for a bakery. I attached what I have so far, but I know there must be a lot mistakes on it. Ultimately, the database will be created with Microsoft Access. From the database, I plan to create forms, generate reports, and do queries. What I'm not sure of, is if my ERD is organized enough that it will be able to do all of the following:
• Tell how much inventory there is at a given time. Provide a running total of how much inventory is left
• Insure that there is plenty of inventory (i.e. cake toppers for special orders) for future sales. Avoid stock-outs.
• Baker should know which products to make the most of and which to make less of to avoid waste of inventory.
Sales (Queries & Reports)
• Tell what items have sold and at what quantity.
• Generate reports of most popular products and slow-sellers.
• Owners should be able to see itemized records of daily, monthly, and yearly sales
Order Taking (Forms)
• Orders will be assigned a unique number # for every order.
Also, I'm unsure of is how to do inventory of the bakery, since the process starts with ingredients then they're turned into finished goods. I wasn't sure if I should add a recipe entity.
Can someone please point out all the mistakes and tell me in which direction I should go on next? Any feedback is appreciated.