-- Invoice System Database Project --

This repository contains the database i created for a course assignment. The idea was to design a complete relational database from scratch and show that i understand data modeling, relationships, normalization and how to maintain data integrity. I chose an invoice system because it has clear entities and realistic workflows, which made it a good fit for practicing proper structure.

The database is built so that multiple companies can use the system without ther data being mixed together. Every user and customer belongs to a specific company, and invoices are always tied back to both a customer and the user who created them. This helped me keep structure clean and avoid unnecessary duplication.

The schema includes tables for companies, users, roles, user-role connections, customers, invoices and invoice lines. Users and roles use a many-to-many relationship, while most other relations are one-to-many. Primary keys and foreign keys are used consistently to keep relationships valid and prevent data from becomming disconnected.

I tried to think about security and correctness early in the design. Foreign keys ensure that invoices cannot reference customers or users that do not exist. A unique constraint on company_id and email prevents duplicate user accounts within the same company. Invoice status is stored as an ENUM to avoid invalid values, and CHECK constraints prevent things like negative amounts or due dates that come before invoice dates. ON DELETE CASCADE on invoice lines ensures that when an invoice is removed, its line items are removed automatically.

Indexes were added where they make sense, mainly to speed up searching customers by name and listing invoices by customer and date. I tried not to add more indexes than necessary since that can slow down inserts. To make some queries easier to work with, i also created two views: one that summarizes total sales per customer, and one that shows a simplified invoice overview. There is also a small stored procedure that inserts new invoices in a consistent way.

Working on this project taught me how much structure and security can be handled directly in the database, before any application code is written. It helped med understand how important relationships, constraints and indexing really are to keeping data accurate and the system reliable.

TO recreate the database, run: SOURCE schema.sql; SOURCE data.sql;

OR open the files in MySQL Workbench

The example queries used for testing the system are in queries.sql.

The mockdata in this project was generated on 2025-11-19 18:27:15.227591426 UTC by Fabricate v3.2.2

Enjoy exploring the project!