Database Design for Customer Management System
This Database Design for Customer Management System or Customer Relationship Management System, is one of the DBMS Mini Project in which the customers of an enterprise are the most important asset of any business can have.
Depending on the kind of industry, a business enterprise may also sell to the same customers over and over.
A purchaser database is the collection of statistics that is gathered from each person.
The database may additionally encompass contact data, just like the person’s name, address, telephone number, and electronic mail address.
The database can also also include beyond purchases and destiny needs.
DATABASE DESIGN FOR HELLO KITTY CAFE CUSTOMER MANAGEMENT SYSTEM
The previous way of selling the items for foods in other café was not turned to be effective that much to the users and the buyer, it wasn’t that effective because they have nothing to do when it comes in storing data or recent buy items of the buyers/customer.
So, this Kitty Café Customers Service System has introduced and developed for the satisfaction of user and of the customer.
This system has made to lessen the hardship of the user/seller while using, editing and updating the existing data.
Since the Kitty Café was nothing to do with the system since they have none, this can be effective and much way better to use for them.
Moreover, this system is designed for the particular need of the seller to carry out an update of information in a smooth and effective manner.
This system reduce as much as possible any errors while entering any data to this system.
Features:
- Manage Products
- Manage records
- Easy in updating data
- Organized Information
- Effective at storing data
DATA DICTIONARIES
Table 1: Tblcustomer
| FIELD NAME | DESCRIPTION | TYPE | LENGTH |
| CUSTOMER_ID | Customer ID number | int | 11 |
| FIRSTNAME | Name of customer | varchar | 50 |
| LAST_NAME | Sure name of customer | varchar | 50 |
| ADDRESS | Address of the Customer | varchar | 50 |
| Email of the Customer | varchar | 50 | |
| CONTACT_NO | Contact of the customer | int | 11 |
| GENDER | Gender of customer | varchar | 50 |
Table 2: TblEmployee
| FIELD NAME | DESCRIPTION | TYPE | LENGTH |
| EMPLOYEE_ID | Employee ID number | int | 11 |
| FIRST_NAME | Name of employee | varchar | 50 |
| LAST_NAME | Sure name of employee | varchar | 50 |
| JOB_TITLE | Title of the Job | varchar | 50 |
| CONTACT_NO | Contact of Employee | varchar | 50 |
| HIRED_DATE | Hired date of employee | int | 11 |
| SALARY | Salary of employee | int | 11 |
Table 3: TblIncome
| FIELD NAME | DESCRIPTION | TYPE | LENGTH |
| CAPITAL | Capital | int | 11 |
| EMPLOYEE_ID | Employee ID number | int | 11 |
| PROD_ID | Product ID | int | 11 |
| PROFIT | Profit | int | 11 |
| INCOME | Income per month | int | 11 |
Table 4: TblLocation
| FIELD NAME | DESCRIPTION | TYPE | LENGTH |
| LOCATIONT_ID | Location number | int | 11 |
| PROVINCE | Location within the country | varchar | 50 |
| CITY | City of province | varchar | 50 |
| MUNICIPALITY | Municipality | varchar | 50 |
Table 5: TblProduct
| FIELD NAME | DESCRIPTION | TYPE | LENGTH |
| PRODUCT_ID | Number of product | int | 11 |
| NAME | Name of product | varchar | 50 |
| DESCRIPTION | Product description | varchar | 50 |
| PRICE | Price of product | int | 11 |
Table 6: TblTransaction
| FIELD NAME | DESCRIPTION | TYPE | LENGTH |
| TRANS_ID | Transaction ID number | int | 11 |
| CUSTOMER_ID | ID number of customer | varchar | 50 |
| PROD_ID | Product ID number | varchar | 50 |
| LOC_ID | Location ID number | int | 11 |
| DATE_TIME | Day of transaction | date | 11 |
ENTITY RELATIONSHIP DIAGRAM
ERD of Management System for Customer 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
Inquiries
If you have any questions or suggestions about Customer Management System Database Design, please let’s me know by dropping your comment below.
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 →