Erd of a Book Store
Problem Statement
We want to create an ERD of a book purchase from one of our employees by a customer. This includes information about various orders from a range of customers served by different employees. Also the information of the book and their quantity and presence or absence in the inventory has to be taken into consideration. It is our aim to efficiently construct an ERD which allows us to easily and reliably get relevant information needed for running the business smoothly.
We will assume that every employee can serve many different customers, but that every customer will always be served by the same employee. Furthermore every customer can have various orders, while every order is only from a single customer. Orders can contain more than one book and the quantity of book is not limited to 1, while of course every book can be found within different orders.
Data Entities
Employee: This entity is to identify the employees, link them with the customers they serve and the respective orders of those customers they handle. Furthermore it contains information about the employees working history in the book store.
Customer: The entity for customers is will help us determine which employees are handling their orders, what their orders and their order history are, who they are and where their shipping address is.
Order: This entity is about identifying which books are ordered, who ordered them and by whom the order and customer are served. Also It contains information about the total price of the order and the date when the order came in.
Book: The book entity will consist of required information about the book, such as the name of the book, the category of it, the authors name and of course the price for which it is sold.
Inventory: The inventory entity is here for handling information about the status of our inventory such as what quantity of what book do we still have, what we need to re order and who the supplier or publisher is we can order it from.
ERD Drawing with Relationships
Every employee can serve many customers, but due to our assumption, ever customer will always be served by the same employee.
Customers can place orders for books (1 or more) and can have more than 1 outstanding order at any given time, but every order can only be linked back to one single customer.
Many orders can contain many books. And order is not limited to one book only. Also every book can be ordered by different customers at different times and therefore it can be in many orders at once.