Rare Book is an Australian company that deals with new and used books in the region. The company runs its business by purchasing books from the market and selling them to its customers. The company is located in Melbourne, Australia. The objective of the company is to sell the new or second hand books to its customers. To serve the purpose, Rare Book also buys new/used books from multiple booksellers or publishing companies. Currently, the company is running its business through a front shop setup. In order to buy a book from a bookseller, it simply sends the book details, a book inquiry, to booksellers. After receiving the quotation, it decides for the best suitable quotation and thus approves the quotation. Rare Book also purchases used books from its old customers or any people who come to it to sell a book. Depending upon the condition of the book, it either buys the book on the spot or declines the offer if book condition is not that worth.
Rare Book has scaled its business upon distinguish features. The main features of Rare Book business are;
Rare Book Owner:
Jerome KickStorm is the owner of Rare Book Store. He is at the highest level of the organization chart as he is the overall owner of book store. All processes are first discussed with owner and then executed within the book store. By processes we mean the processes involving purchase and sell of books, books management, catalogue information and all customers’ information.
Henri M. is the sales manager in Rare Book Store. He is the main person that handles both sales and purchases of new/used/old books. Depending upon his responsibilities, his role has been divided into two types, one is Sales and other is Purchase, as Sales Manager has to deal with different stakeholders under each type. For Sales, he collects data from salesmen and for purchase purposes; he needs to either interact with booksellers or customers. By customers we mean people who are interested in selling used or old books to Rare Book Store.
Salesmen are the personnel that are physically present in the book store in order to sell the books to customers. Salesmen are also supposed to update book catalogues on daily basis and to maintain an up-to-date book inventory/stock with in the store.
Booksellers are the shop keepers from whom Rare Book purchases new/old books.
Customers are the people who want to either buy books from Rare Book store or sell the old books back to the store.
Since all the business is running through a front shop, therefore, Rare Book has figured out a prominent fall in sales records. As we already know that Rare Book has a big catalogue which contains over thousands of books, but still a very small portion of books are sold on daily basis. Upon analyzing the reasons of falling sales records, Rare Book store has drawn a few conclusions and hence decided to enhance its business accordingly. Some of the conclusions drawn are:
1. Rare Book store has no online medium through which it can expand its business!
2. There are a very limited number of customers associated with Rare Book store!
3. Are all the books available for sale, are up to the market demand?
4. What are more possible ways to enhance Rare Book store business?
Rare Book is looking forward to enhance their business by starting a joint venture with Penguin Books. Penguin Books is an American based company which also runs the same business. This joint venture is in favor of both the companies as Rare Book has a big book catalogue but less customers and Penguin Books has a large number of customers but less books to sell. Therefore, both the companies have agreed to execute their business with each other on commission basis. The commission has been decided on the terms that when Rare Book will sell any book to Penguin customer, Rare Book will be liable to pay the commission to Penguin Books for each sale/customer. Similarly, when Penguin Books will sell any book (Rare Book) to its customer, Penguin Books will be liable to pay the commission to Rare Book for each sale/book.
First of all, Rare Book requires a web portal in order to run its business over the internet. After having its very own web portal, Rare Book will synchronize its business with Penguin Books so to increase its sales. Rare Book Online Store will contain the information of following data;
- There will mainly be two views of the web portal, one will be from Admin perspective and one will be for common customers` perspective.
- System will be able to store complete information about books catalogue so that catalogue can be presented to online users.
- System will be able to store information about books inventory so that all books could be available for customers at time of need.
- System will be able to store information about all the booksellers that are associated with Rare Book store so that books could be inquired or purchased online thus minimizing the paper work.
- System will be able to store customer’s information that are associated with Rare Book store and buy or sell books from or to Rare Book store.
- System will be able to distinguish between Rare Book customers and Penguin Books customers as per defined commission rules.
- System will be able to sell or buy books both online and offline, from or to booksellers/publishers and customers respectively.
- System will be able to manage book shipping and track in case a book is sold or purchased online.
- System will be able to verify the credit card information in case of online transactions.
- System will be able to scan book covers so that thumbnails could be provided for customers searching books online on Rare Book store web portal.
- System will be able to maintain transactions in between Rare Book and Penguin Books.
- System will be able to calculate Penguin Books` commission in case a sale is made on Penguin Book`s customer.
- System will be able to generate different reports depending upon the selected criteria by Sales Manager or other stake holders.
- System will be able to provide Search engine to its online users.
After achieving the above goals, Rare Book store will be able to run its business more effectively. Not only the decision of joint venture with Penguin Books will be proved fruitful but also the decision of creating a complete web interface will be proved productive! The interface will be developed in such a way so that maximum issues could be recovered that cause fall in Rare Book store sales.
Maintaining all books`, customers`, booksellers’ records online will become easy, efficient and time consuming. It will also be helpful to cut cost within the store as one salesman could be efficiently trained on the developed system and there will no need to hire more salesmen.
Reports could easily be drawn to analyze the sales and purchases records and to measure the overall business success ratio over different criterion.
Entity Relationship Diagram:
In order to draw the ERD effectively, I have divided the Rare Book Store`s existing and required data information and business processes into following sub parts so that ERD can easily be understood. The relationship notation used in the ERD is Crow`s Foot and ERD has been created in MySQL Workbench.
Books Catalogue Management:
Books Catalogue Management will deal with the processes to manage the overall catalogues of books available in the System. It will include details of books shelves locations, current statuses and conditions of the books. It will also include adding new book records or update existing book records.
Book Inventory Management:
Book Inventory Management will deal with processes of book inquiries that will be send to different booksellers or publishers, book quotations that will be received against inquiries and book receipts that will be generated upon book purchasing from booksellers or publishers.
User Management will deal with the processes of managing the overall users that are associated with Rare Book Store business in any way. It will include adding new user records, updating or deleting existing user records.
Sales Management will deal with the processes of selling books to customers. It will also deal with book shipment in case customer’s requests for shipment and it will also include credit cards verification if customers wish to pay by credit cards.
Procurement Management will deal with process of purchasing old/new books from different people like customers, booksellers, publishers.
Penguin Commission Calculation:
Penguin Commission will deal with the processes of calculating Penguin Book`s commission in case a sales transaction is made to Penguin`s customers.
From the above data, it has been analyzed that System needs to store information about following entities;
- Customer Management
Following are the attributes of above entities.
Book_ID (PK), Book_ISBN, Book_Title, Book_Publisher, Book_Edition, Book_Price, Book_Call#, Binding_Type, Book_Category, Book_Status, Book_Condition, Comments
Inquiry_ID (PK), Inquiry_Date, Inquiry_To (FK from Bookseller_ID/Publisher_ID), Inquiry_By (FK from User_ID), Inquiry_Method, Book_Details, Inquiry_Status, Comments
Quotation_ID (PK), Quotation_Date, Received_By (FK from UserID), Received_Against (FK from Inquiry_ID) Quotation_Method, Received_From (FK from BooksellerID/PublisherID), Book_Details, Quotation_Status, Quotation_Price, Comments
Purchase_ID (PK), Purchase_Date, Purchased_By (FK from User_ID), Purchased_From (FK from Bookseller_ID/Publisher_ID/Customer_ID), Purchased_Method, Payment_Method, Purchased_Against (FK from Quotation_ID), Book_Details, Purchased_Status, Purchased_Amount, Comments
Receipt_ID (PK), Receipt_Date, Receipt_From (FK from Bookseller_ID/Publisher_ID), Receipt_By (FK from User_ID), Receipt_Against (FK from Purchase_ID), Receipt_Details, Receipt_Price, Receipt_Status, Comments
Sale_ID (PK), Sale_Date, Book_Details (FK from Book_ID), Sale_By (FK from User_ID), Sale_To (FK from Customer_ID), Sale_Method, Sale_Details, Sale_Price, Payment_Method, Penguin_Customer, Require_Shipment, Comments
Shipping_ID (PK), Shipping_Date, Shipped_By (FK from User_ID), Shipped_To (FK from Customer_ID), Shipped_Against (FK from Sale_ID), Shipping_Method, Shipping_Days, Book_Details (FK from Book_ID), Shipping_Status, Shipping_Cost, Comments
Commission_ID (PK), Commission_Date, Calculated_Against (FK from Sale_ID), Commission_Rate, Commission_Amount, Commission_Status, Calculated_By (FK from User_ID), Comments
User_ID (PK), User_Name, User_CNIC, User_Address, User_Contact#, User_Email, User_DOB, Work_Experience, User_Salary, Comments
Bookseller_ID (PK), Bookseller_Name, Shop_Name, Bookseller_CNIC, Shop_Address, Bookseller_Contact#, Bookseller_Email, Member_Since, Comments
Publisher_ID (PK), Publisher_Name, Publisher _CNIC, Publisher_Address, Publisher_Contact#, Publisher _Email, Member_Since, Comments
Customer_ID (PK), Customer_Name, Customer_CNIC, Customer_Address, Customer_Contact#, Customer_Email, Credit_Card#, Comments
Book_Management_ID (PK), Management_Date, Managed_By (FK from User_ID), Managed_To (FK from Book_ID), Reason, Comments
RBUser_Management_ID (PK), Management_Date, Managed_By (FK from User_ID), Managed_To (FK from User_ID), Reason, Comments
Bookseller_Management_ID (PK), Management_Date, Managed_By (FK from User_ID), Managed_To (FK from Bookseller_ID), Reason, Comments
Publisher_Management_ID (PK), Management_Date, Managed_By (FK from User_ID), Managed_To (FK from Publisher_ID), Reason, Comments
Customer_Management_ID (PK), Management_Date, Managed_By (FK from User_ID), Managed_To (FK from Customer_ID), Reason, Comments
Following are the business rules that Rare Book Store follows in order to run their business effectively;
- One customer can buy many books but each book will be sold to one customer only.
- Books will be sold to customers only.
- Books can be purchased from booksellers, publishers and customers.
- Many books can be purchased from bookseller/publisher/customer but each book will be purchased from either bookseller or publisher or customer.
- Many Rare Book Store users can manage books catalogue.
- Many Rare Book store users can manage users` (booksellers, publishers, customers etc) records.
- Penguin Books` commission will be calculated for each sale, done on Penguin Book`s customer.
- Credit Card information will be verified if amount is paid via credit card at time of sales.