I used to work for a company based in Atlanta, GA. This company is a wholesaler of furniture and spa chairs. Since this was a small company, I had opportunities to be exposed to different functional departments and learnt a lot on the job. I was more specialized in operations and inventory management system so most of my work was collaborating with Sales department, warehouse supervisors and purchasing department. During my time there, although they have other approaches to store the data, there was no consolidated database for all employees to access to. To resolve their problem, I developed a database on Google Drive (Google Sheets) to make it accessible for anyone from anywhere at any time. The project was one of my biggest accomplishments in that job that I’m still proud of until today. That valuable experience inspired me to work on this personal project - wholesale management database where I utilize MySQL to build a consolidated and user-friendly database for internal use.
When it comes to information system, we cannot eliminate human error since the data entry process is required no matter how advance the system is. We know that the fewer steps human is involved in, the lesser errors they can make. That being said, my goal in this project is to mitigate human errors and simplifying the data entry process. The idea to make this goal achievable is to use Triggers and Stored Procedures. Triggers are utilized to ensure that all the inputs are valid and contributed meaningfully to the database. Whereas Stored Procedures are optimized to minimize efforts and time by automated insert/update statements. Block of codes in each Stored Procedure can update/insert rows in associated tables accordingly.
MySQL Workbench 8.0
- ERR Diagram
- Tables: All tables in the database
- List of Procedures and Triggers
- Procedures: To add new employee, supplier, customer, product/inventory
- Sales Order procedure: To add new order
- Purchasing Order procedure: To add new purchasing_order
- Resolved accounts_check: To ensure accounts is resolved correctly
- Sample data: Insert sample data using procedures