- Identification of the entities.
- Restaurants – general info about the restaurant
- Restaurants hours – working hours of the restaurant, in case restaurant will differentiate working hours some day.
- Worker
- Customer
- Menu item – what the restaurant serves;
- Menu item variation – to store data about different pizzas.
- Category – what category menu item belongs to
- Size – size of the menu item
- Type – type of menu item
- Topping – pizzas toppings
- Order – order of the customer.
- Definition of tables.
- Restaurants – general info about the restaurant
- Restaurants hours – working hours of the restaurant
- Worker
- Customer
- Menu item – what the restaurant serves;
- Menu item variation
- Category – what category menu item belongs to
- Size – size of the menu item
- Type – type of menu item
- Topping – pizzas toppings
- Order – order of the customer.
- Proof of normalization.
1NF
There is no data duplication in tables. Data is atomic (there are no value of attribute like John, David.) therefore tables are in the 1NF.
2NF
All tables have single attribute for primary key (there is no compound keys), therefore they are in 2 NF.
3NF
Lets try to find functional dependency among non-key attributes inside our tables.
Look in the Order table
Are city and country dependent on Zip code? Certainly we can find them by zip code. Therefore we need to separate them into new group.
Zip code
Now all tables are in 3NF.
4) Produce an Entity-Relationship Diagram to complete the Logical Design
5) Evaluation of your design
Design is very flexible. If restaurant will decide to add some completely different menu positions (new dishes, beverages, etc.) it will be possible to do without changing DB structure that will simplify their work and save money. Moreover, new ingredients (toppings), sizes, pizza types could be added without any pain, because we have dropped this information to a separate tables. Such abstraction adds huge flexibility to further development on the project. Tables are build considering future changes so extensions will be added easy without need to redesign. However, such approach increase initial costs of the project and makes the system more complicated, (such flexible design adds many additional joins, but this should not affect performance due to a small database).
References
The Basics of Normalizing a Database. (n.d.). Retrieved December 4, 2014, from http://databases.about.com/od/specificproducts/a/normalization.htm
Introduction to Data Normalization: A Database "Best" Practice. (n.d.). Retrieved December 4, 2014, from http://agiledata.org/essays/dataNormalization.html
Database design basics. (n.d.). Retrieved December 4, 2014, from https://support.office.com/en-ca/article/Database-design-basics-1eade2bf-e3a0-41b5-aee6-d2331f158280
The Basics of Good Database Design in Web Development. (n.d.). Retrieved December 4, 2014, from http://www.onextrapixel.com/2011/03/17/the-basics-of-good-database-design-in-web-development/
A Quick-Start Tutorial on Relational Database Design. (n.d.). Retrieved December 4, 2014, from http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
