Jose Herrera
asked on
How can I improve this ERD for a bakery database?
Hi,
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:
Inventory (Reports)
• 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.
Thank You.
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:
Inventory (Reports)
• 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.
Thank You.
Dale Fye
Jose, Just checking, is this a homework assignment?
ASKER
It's for a class project.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Other issues with answering "tell me whats wrong with my design" questions is the scope of the recommendations.
For example, your question title is: "How can I improve this ERD for a bakery database"
...yet some of your subsequent questions do not relate directly to the ERD:
For example:
Generate reports of most popular products and slow-sellers.
Owners should be able to see itemized records of daily, monthly, and yearly sales
...are, in essence, reporting aspects
To approach this from purely the standpoint of the ERD, ...here are some of my suggestions:
1. Add the following fields the customer table:
CompanyName
Email
Fax
2. Use a consistent naming convention, for example:
All Primary keys should end with "ID"
Do not include spaces in your names.
3. To me, you may not need a separate table for "Special Orders", ...perhaps a Boolean field in the Orders table would do...:
SpecialOrder: Yes/No
4. Differentiating between entities like Products and Ingredients can become an exercise in futility if these things are not very clearly defined.
In some cases a "Product" can end up being an "ingredient" or vice versa.
For example a Bagel can be a product you make from ingredients, ...but it can also become a "ingredient" in the case of flavored bagel chips.
Or suppose that another bakery wants to buy a sack of flour(an ingredient) from you as a "product"?
5. To me a "recipe" table is not needed in this database.
6. Rename the table: Order/Purchases, to just: "Orders"
7. Rename: Product Ingredients List, to simply,: ProductIngredients
8. You seem to be missing a ProductCategory table...?
To me questions like:
• Insure that there is plenty of inventory (i.e. cake toppers for special orders) for future sales. Avoid stock-outs.
...and:
• Baker should know which products to make the most of and which to make less of to avoid waste of inventory.
...are managerial functions, and deal with things like maximizing output.
Maximizing output is not a simple concept, and may have to be dealt with with special formulas, functions , or algorithms.
Furthermore, in most production systems, the "employee" (Baker) is not called upon to do these calculations.
In most cases the baker is simply given a list of things to bake.
;-)
...and since no manager can predict "future sales" with 100% accuracy, having "plenty of inventory", ...is a somewhat vague requirement.... (how much is "plenty" for each product/ingredient and for what time period?)
So, as you can see, we could spend all of this month "suggesting" improvements.
So this leads to the main problem with these purely "academic" exercises.
Is this a neighborhood bakery of a large conglomerate Bakery?
Did this assignment start with the requirements, and you created the design?
Or were you given the requirements and the design?
...etc
...Without knowing *all* the aspects of this "theoretical" business, ...it is hard to make any read, meaningful suggestions
So you may want to narrow the scope of this question so that you can get more focused recommendations.
JeffCoachman
For example, your question title is: "How can I improve this ERD for a bakery database"
...yet some of your subsequent questions do not relate directly to the ERD:
For example:
Generate reports of most popular products and slow-sellers.
Owners should be able to see itemized records of daily, monthly, and yearly sales
...are, in essence, reporting aspects
To approach this from purely the standpoint of the ERD, ...here are some of my suggestions:
1. Add the following fields the customer table:
CompanyName
Fax
2. Use a consistent naming convention, for example:
All Primary keys should end with "ID"
Do not include spaces in your names.
3. To me, you may not need a separate table for "Special Orders", ...perhaps a Boolean field in the Orders table would do...:
SpecialOrder: Yes/No
4. Differentiating between entities like Products and Ingredients can become an exercise in futility if these things are not very clearly defined.
In some cases a "Product" can end up being an "ingredient" or vice versa.
For example a Bagel can be a product you make from ingredients, ...but it can also become a "ingredient" in the case of flavored bagel chips.
Or suppose that another bakery wants to buy a sack of flour(an ingredient) from you as a "product"?
5. To me a "recipe" table is not needed in this database.
6. Rename the table: Order/Purchases, to just: "Orders"
7. Rename: Product Ingredients List, to simply,: ProductIngredients
8. You seem to be missing a ProductCategory table...?
To me questions like:
• Insure that there is plenty of inventory (i.e. cake toppers for special orders) for future sales. Avoid stock-outs.
...and:
• Baker should know which products to make the most of and which to make less of to avoid waste of inventory.
...are managerial functions, and deal with things like maximizing output.
Maximizing output is not a simple concept, and may have to be dealt with with special formulas, functions , or algorithms.
Furthermore, in most production systems, the "employee" (Baker) is not called upon to do these calculations.
In most cases the baker is simply given a list of things to bake.
;-)
...and since no manager can predict "future sales" with 100% accuracy, having "plenty of inventory", ...is a somewhat vague requirement.... (how much is "plenty" for each product/ingredient and for what time period?)
So, as you can see, we could spend all of this month "suggesting" improvements.
So this leads to the main problem with these purely "academic" exercises.
Is this a neighborhood bakery of a large conglomerate Bakery?
Did this assignment start with the requirements, and you created the design?
Or were you given the requirements and the design?
...etc
...Without knowing *all* the aspects of this "theoretical" business, ...it is hard to make any read, meaningful suggestions
So you may want to narrow the scope of this question so that you can get more focused recommendations.
JeffCoachman