Hello, Friends, This is the second story for Billing System updates. I have done with DB Design.
Totally 16 Tables I have created in the MySQL DB.
ORGANIZATION
The Organization table contains only the Shop or Company information and nothing more.
USER
The user table contains the User data. Everybody treated as a User i.e. Employees, Customer, Vendor everybody called User.
ROLE
The Role table is having available Role that supported by the System. We can create our own Role and assign it to the User.
USER_ROLE
The user_role table is a join table of user and role table. it contains how many roles assigned to a User and how many users assigned to a Role. It is many to many relation tables.
EMPLOYEE
The Employee table should have employee information. At the time of user registration, the user information stored in the user table, and other employee-related information stored in the employee table. The user table is a parent table and the employee table is the child table of User.
CUSTOMER
The Customer table should have customer information. At the time of user registration, the Customer related information stored in the Customer table. The Customer table is a child table of User.
VENDOR
The Vendor table should have Vendor information. The vendor is nothing but the product supplier of the Shop owner i.e. The vendor is a Seller who sells wholesale rates to the Shop owners. At the time use registration, the Vendor details stored in the Vendor table. The Vendor table is a child table of User.
EMPLOYEE, CUSTOMER, AND VENDOR
All the above three tables hold the User data but each table should have a different column so common information stored in the User table. Rest of the information stored in the respective table (Employee, Customer, and Vendor)
PRODUCT
The Product table should have Product details which are available in the Shop. The Product table is the child table of Category , Tax, and Vendor table. At the time of implementation, I will explain clearly the purpose of this relationship.
PRODUCT_ITEM
This table contains the product’s SKU (Stock Keeping Unit) of each product_id in the Product table. If you buy 100 units of the same design shirt. These 100 same design short comes under one Product. let say Product Id is Shirt001. Now, this product contains 100 quantity. Each Shirt assigns a unique SKU. Mostly it is a barcode where Shopkeeper scans the barcode at the time selling the product with the Customer.
PRODUCT_CATEGORY
This Category table contains various product categories. The product table is a child table of this Product Category Table.
BILLER
The Biller table contains the billing information of various Products purchased by Customers. This table is a Child table of Customer, Product, Product Item, and Tax Table.
PURCHASE_ORDER
This table contains the Order information that made against the Vendor. For Example, if the Shop owner purchases 100 Shirts from the Vendor. This information stored in the PURCHASE ORDER Table. The Purchase Order table is a child table of the Vendor Table.
STOCK
The Stock table contains the stock details of available products. The Stock table is a child table of Product and Vendor Table.
OFFER
The Offer Table contains various Offer information. Using this table we can map the offers to a particular Product. We can remove the offer from a particular Product.
TAX
This Tax table contains the HSN code for various products. These codes are internationally recognized. This HSN code classifies products all over the world. Each HSN code mapped with a particular GST Tax Percentage figure (like 5%, 10%, etc). For Example. The Shirt type of product has an HSN code and that HSN code assigns with a GST Percentage. Whatever product sells by Shop Keeper those Product maps with the right HSN codes. So, easily we can find out what is the GST Percentage for a particular Product.
ADDRESS
This address table contains the address details of User data. This is the Parent table of all other tables which need Address data.
This is the initial structure of my DB Design. Based on the requirement while developing, I will remove the columns or add a new Column.
Please check this video link for the DB Design explanation:
Billing System DB design explanation @ ITGARDEN
