Invoice System Database Schema

Hello everyone,

I am building an invoicing system, and facing somewhat of a small issue regarding the final database schema.

The system requires to be able to create 3 types of invoices:

one for goods that the company has bought from a supplier,
another one for charging a project, task and vehicle( the task and vehicle belong to a project)
and a final one charging a client

After a bit of deliberation, i came up with the idea of creating 3 types of tables for each one of those invoice types. Then use a many to many relationship on each of those 3 tables to a single invoice_products table that holds all the products for all the invoices.

My main sources of concern are:

Go with the above scenario, and make the invoice_products table many to many polymorphic. Total Tables: 4
Again following the above scenario, but instead of having a single invoice_products table for all 3 types of invoices, use a separate invoice_products table for each of them. Total Tables: 6
Final option, create a single invoice table, and use null-able fields and a single type field that will be used to distinguish between the 3 types of invoices, and use the invoice_products table for all the products. Total Tables: 2

Most of the above data will be used in combination with other data to create daily and monthly reports. I am trying to push the load on the database everywhere possible rather than in my code base.

Each product belongs to one of 5 product categories, witch in my reports has to be calculated separately. So if a client has bought 5 products, one from each of the categories, in his reports i have to show each cost separately, and also a sum of all of them.

Any suggestion or tip is welcome.

jlrdw

6 years ago

Level 75

See https://laracasts.com/discuss/channels/general-discussion/rental-management-db-schema

Alt image

Just example data.

A little JS and ajax you can do a popup edit, or use a modal.

Alt image

I'd keep things simple and stick with one to many relations for business apps.

I'd advise getting a good visual query designer, and see:

https://www.mysqltutorial.org/

1 like

Sidart

6 years ago

Level 12

That will work for the supplier and client invoices.

With the vehicle charge there is a need to create multiple vehicle charge rows in one invoice.

To clarify, the total fields required for all three types are:

Supplier id, client id, project id, task id, vehicle id plus fields specific to each type, like code that is only required for the supplier, date etc. Going with a simple one to many, there will be the need to have null-able fields for supplier and client id's if the invoice was for a vehicle charge, and i am not a fan of null-able fields.

I implemented it with 3 invoice types, one for client, one for supplier and one for vehicle charge and each have a morphMany to the invoice products table.

This way i am able to pull each data separately, and since most of the invoices are of vehicle charge type i don't have to pull all the rest.

The entire app is build like a SPA with vuejs etc, so i also broke down each invoice to its own component both for the front end and the Backend.

That said, i really appreciate your input.

Please or to participate in this conversation.