Cosmetics Warehouse Management Database Design

The problem: when a business needs tight control over the flow of goods

The team discussed and chose a cosmetics warehouse management problem — a domain with its own quirks: products have expiration dates, need to be tracked per warehouse, and every inbound/outbound transaction has to be recorded accurately to avoid inventory discrepancies. The hypothetical business needed a system to manage multiple warehouses at once, track products by location, manage the inbound process from suppliers, the outbound process to customers, and most importantly — keep data accurate every time a transaction occurs.

This was one of the first projects to approach database design from scratch — not just writing SQL statements, but having to ask: what entities does this system actually need, and how do they relate to each other?

Role: from modeling to implementation on SQL Server

Led the following work:

• Designed the ERD in draw.io based on the database structure the team had agreed on
• Built the tables in Microsoft SQL Server
• Added sample data to the system (combining data the team built manually with AI-generated data, then edited to match cosmetics warehouse operations)
• Helped finalize the report and presentation slides

The process: when the hardest part isn’t writing SQL, but thinking through the relationships correctly

The biggest challenge wasn’t query syntax — it was correctly modeling the detail tables, especially CHITIETPHIEUNHAP (inbound transaction details) and CHITIETPHIEUXUAT (outbound transaction details). Getting the relationships right between SANPHAM (Product) and CHITIETPHIEUNHAP, between CHITIETPHIEUNHAP and PHIEUNHAP, and the equivalent on the outbound side, took multiple rounds of team discussion to make sure the model accurately reflected the business logic while avoiding redundant data.

The workflow followed standard database design steps:

1. Analyze the warehouse management problem and identify the necessary entities and attributes
2. Design the ERD, determining cardinality between entities
3. Convert the ERD into a relational model (RD)
4. Normalize the data to remove redundancy and ensure integrity
5. Design constraints: Primary Key, Foreign Key, Composite Key, CHECK Constraint, UNIQUE Constraint
6. Build the actual database in SQL Server and generate sample data
7. Write queries to extract data, using JOIN, GROUP BY, Aggregate Queries, and Common Table Expressions (CTE)
8. Build Triggers to enforce data consistency

Results

• Designed a complete relational database with 10 tables
• Successfully implemented in Microsoft SQL Server with full integrity constraints
• Deployed triggers for key operations: checking production/expiration dates, automatically updating stock on outbound transactions, and preventing deletion of inbound records to protect transaction history
• Wrote representative analytical queries: total monthly revenue, total inbound value per supplier, staff count per warehouse, and months with below-average revenue
• Completed the full report and presentation as required by the course

The biggest takeaway

This project served as the SQL and database design foundation before going deeper into Data Analytics and Data Engineering in later coursework. The clearest lesson was the importance of normalization — a design that looks simple can still lead to duplicate or inconsistent data once the system runs in practice if keys and relationships aren’t carefully defined. The role of Triggers in automatically enforcing data correctness was also a concept understood much more deeply through this project.

If I did it again

• Add Stored Procedures to encapsulate more complex business logic
• Build Views to support reporting and avoid rewriting long queries repeatedly
• Design user permissions for the system
• Normalize the database further, especially the detail/transaction tables
• Choose a dataset with more real-world relevance and analytical potential
• Write more analytical queries aimed at business insight, rather than stopping at basic statistical queries