Convenience Store Sales and Inventory System Database Design Project
This Convenience Store Sales and Inventory System Database Design Project focuses on the store’s sales and inventory system.
Since they are using the manual recording of transactions, sometimes they forget to record the transaction of some customers.
Using a manual recording, there’s a high possibility of errors and miscomputations of sales like the record are being lost because of the busyness of the staff or the assigned personnel.
Since the system is a computerized one, it can be easy for the owner to manage the activities in their business.
Thus, this system can lessen the paper works in this area and it won’t be time-consuming to the owner and staff.
This system was created to be used in tracking the sales and inventory of the store.
It gives you the ability to check on your products, manage suppliers and many more.
It enables users to create, update and store products and transactions that are happening. In this system, a single transaction entry records necessarily details of the customer, products purchased, price and date while also updating inventory levels.
It is helpful to those who have a convenience store and that are using manual recording of the transaction. It also provides a computerized system for maintaining records of the products and the clients.
It is more efficient and reliable to use. Using this system, you can avoid human errors, data manipulations, and especially data inconsistency and redundancy.
It is user-friendly and it is used to improve efficiency in recording for each transaction.
Instead of maintaining separate record-keeping processes for the payment and inventory adjustment, you can manage each aspect with a single entry.
Every time there is a sales transaction, the sales and inventory system automatically updates the store’s database to reflect the corresponding requirement in terms of inventory and finance.
Data Dictionaries for Convenience Store Sales And Inventory System Database Design Project
Features:
- Inventory Control
- Sales Report
- Order Management
- Price List
These database tables below provide the entire database tables details such as Field Name, Descriptions, Data Types, Character Lengths.
Table 1: tblcustomer
| Field Name | Description | Type | Length |
| CUST_ID(PK) | Customer ID | INT | 5 |
| SALES_ID | Sales ID of the customer | INT | 5 |
Table 2: tblemployees
| Field Name | Description | Type | Length |
| EMPLOYEE_ID(PK) | Employee ID | INT | 5 |
| FIRST_NAME | Employee First Name | VARCHAR | 50 |
| LAST_NAME | Employee Last name | VARCHAR | 50 |
| Employee Email Address | VARCHAR | 50 | |
| PHONE NUMBER | Contact Number of the Employee | VARCHAR | 50 |
| JOB_ID | Employee’s Job | INT | 5 |
| ADDRESS | Employee’s Address | VARCHAR | 50 |
Table 3: tbljob
| Field Name | Description | Type | Length |
| JOB_ID(PK) | Job ID | INT | 5 |
| JOB_TITLE | Title of job | VARCHAR | 50 |
| SALARY | Monthly Salary | INT | 11 |
Table 4: tblproduct
| Field Name | Description | Type | Length |
| PRODUCT_ID(PK) | Product ID | INT | 5 |
| PRODUCT_NAME | Name of the product | VARCHAR | 50 |
| PROD_DESCRIP | Description of the product | VARCHAR | 50 |
| QTY_STOCK | Quantity of Stocks | INT | 11 |
| PRICE | Price of the product per piece | INT | 11 |
| ON_HAND | Products available on hand | INT | 11 |
| DATE_SUP | Date the products delivered | DATE | |
| SUPPLIER_ID | Supplier ID | INT | 5 |
Table 5: tblsupplier
| Field Name | Description | Type | Length |
| SUPPLIER_ID(PK) | Supplier ID | INT | 5 |
| COMPANY_NAME | Supplier’s Company Name | VARCHAR | 50 |
| ADDRESS | Address of the supplier | VARCHAR | 50 |
| PHONE_NUMBER | Contact Number of the supplier | VARCHAR | 50 |
Table 6: tbluser
| Field Name | Description | Type | Length |
| USER_ID(PK) | User ID | INT | 5 |
| USERNAME | Username of the user | VARCHAR | 50 |
| PASSWORD | Password of the user | VARCHAR | 50 |
| EMPLOYEE_ID | Employee ID | INT | 5 |
Table 7: tblsales
| Field Name | Description | Type | Length |
| SALES_ID(PK) | Sales ID | INT | 5 |
| DATE | Date of the transaction | DATE | |
| PRODUCT_ID | Product ID | INT | 5 |
| PRODUCT_NAME | Name of the product | 50 | 50 |
| QUANTITY | Quantity of product purchased | INT | 11 |
| TOTAL_AMOUNT | Total amount to be paid | INT | 11 |
| CHANGE | Change of the customer | INT | 11 |
| EMPLOYEE_ID | Employee ID who cater the transaction | INT | 5 |
ERD for Convenience Store Sales and Inventory System Database Design Project
Figure 1 Proposed CG Dungog Sales and Inventory Entity Relationship Diagram shows the system entity relationships in each entity and their supposed functions in each relationship.
For More Database Design Examples
- ER Diagram for Sales and Inventory System Database Design
- Ordering System Database Design with ERD
- ER Diagram for Water Refilling Station System Database Design
- Database Design for High School Enrollment System
How to read an ER diagram
An entity-relationship (ER) diagram documents the database schema: entities (tables), attributes (columns), and relationships (foreign keys and cardinality).
- Entity. Rectangle representing a table.
- Attribute. Oval or field for each column.
- Primary key. Underlined attribute name.
- Foreign key. Attribute referencing another entity.
- Relationship. Diamond or line connecting related entities.
Common capstone mistakes to avoid
- Many-to-many without junction table.
- Missing primary key.
- Denormalized redundancy.
- Ambiguous naming.
Where this diagram fits in Chapter 3
- Section 3.3 (Database Design).
- Include the CREATE TABLE SQL script alongside.
- Reference from the class diagram.
- Include a legend to explain the notation for panel members.
Official documentation
Official documentation
Working source code for this system
Download the actual implementation of this system in your preferred language. Each project includes source code, database, and setup instructions for BSIT capstone use.
Frequently asked questions
What is a ER diagram used for in BSIT capstone?
An ER diagram shows the database schema: entities (tables), attributes (columns), and relationships (foreign keys, cardinality). It goes in Chapter 3 alongside the class diagram to communicate the data storage design.
What tool should I use to draw the ER diagram?
Free options: draw.io, Lucidchart free tier, PlantUML, StarUML 30-day trial, Visual Paradigm Community Edition. Paid options: Microsoft Visio, Lucidchart pro, Enterprise Architect. For BSIT capstones, draw.io is the most commonly used free tool.
How detailed does the ER diagram need to be for capstone defense?
Panel members expect the diagram to match the actual system implementation. Include every major class/use case/entity relevant to the system. Omit trivial helper classes. Every diagram element should have a clear justification.
Should I use black-and-white or colored diagrams?
Black-and-white is standard for capstone documentation to match the thesis format. Use color only if it improves clarity. Ensure text is readable at printed size (10pt minimum for labels).
Where does this diagram go in the capstone documentation?
Chapter 3 (System Design and Methodology) typically holds all UML diagrams. Introduce each diagram with a 1-paragraph description explaining what it shows and how to read it.
Frequently asked questions
What is a ER diagram used for in BSIT capstone?
An ER diagram shows the database schema: entities (tables), attributes (columns), and relationships (foreign keys, cardinality). It goes in Chapter 3 alongside the class diagram to communicate the data storage design.
What tool should I use to draw the ER diagram?
Free options: draw.io (browser-based, saves to Google Drive), Lucidchart free tier, PlantUML (text-based, version-controllable), StarUML (30-day trial then reduced feature set), Visual Paradigm Community Edition. Paid options: Microsoft Visio, Lucidchart pro, Enterprise Architect. For BSIT capstones, draw.io is the most commonly used free tool.
How detailed does the ER diagram need to be for capstone defense?
Panel members expect the diagram to match the actual system implementation. Include every major class/use case/entity relevant to the system. Omit trivial helper classes. Every diagram element should have a clear justification. Aim for 1-2 diagrams that fully cover the system, not many partial ones.
Should I use black-and-white or colored diagrams?
Black-and-white is standard for capstone documentation to match the thesis format. Use color only if it improves clarity (e.g., grouping subsystems). Ensure text is readable at printed size (10pt minimum for labels).
Where does this diagram go in the capstone documentation?
Chapter 3 (System Design and Methodology) typically holds all UML diagrams. Introduce each diagram with a 1-paragraph description explaining what it shows and how to read it. Reference specific elements in the surrounding text so panel members can follow the design rationale.
Mary Grace G. Patulada
Programmer & Technical Writer at PIES IT Solution
Mary Grace G. Patulada (pen name ‘Nym’) is a programmer and writer at PIES IT Solution with a BSIT background from Carlos Hilado Memorial State College, Binalbagan Campus. Authored 370+ UML diagram tutorials and capstone documentation guides at itsourcecode.com. Specializes in UML (class, use case, activity, sequence, component, deployment), DFD, and ER diagrams for BSIT capstone projects.
Expertise: UML Diagrams, DFD, ER Diagrams, Use Case Diagrams, Activity Diagrams, Capstone Documentation, PHP · View all posts by Mary Grace G. Patulada →