For our project we have 6 tables. Every table has a primary key. The employee table keeps records of all the employees, the customer table gives the store members’ information, the supplier table gives a record of both stationary and books bought from the supplier, the stationary table shows what stationary item we sell, the books tables shows what books we sell, the invoice table keeps record for a confirm purchase.
Employee(Employee_ID, Fname, Lname, Gender, DoB, Phone_Num, Email, Salary)
Customer(Member_ID, Fname, Lname, Gender, DoB, Phone_Num, Email)
Supplier(Item_ID, Company_Name, Phone_Num, Item_Name, Item_Quantity, Item_Price, Total_Payment)
Stationary(Item_ID, Price, Stock_Num) Foreign Key(Item_Name) Reference(Supplier(Item_Name))
Book(Item_ID, ISBN, Author, Book_Price, Stock_Num) Foreign Key(Item_Name) Reference(Supplier(Item_Name))
Invoice(Invoice_Num, Equipment_Quantity, Book_Quantity, Total_Price, Invoice_Date) Foreign Key(Member_ID, Employee_ID, Item_ID, ISBN) Reference(Customer(Member_ID), Employee(Employee_ID), Stationary(Item_ID), Book(ISBN))
For our application, the user would be able to view each of the table content and also modify it if they want to, like adding, updating, and deleting.
As for the rest of the tables it is practically the same as the others, the user can insert a new item, update an existing item, and delete an existing table.