Invoices and Quotes - Best practices?

I think Quotes and Invoices are different, there's no need to create interface or an abstract class.

@RachidLaasri Thanks. That's somewhat a relief to hear. Keep them separated.

What are your thoughts on an API? Beforehand, or afterwards?

Here are some videos you might find helpful for a way to approach the task at hand.

https://laracasts.com/lessons/controller-cleanup

https://laracasts.com/lessons/responsibility

https://laracasts.com/lessons/coding-with-intent

And there is a whole series on OO

https://laracasts.com/series/object-oriented-bootcamp-in-php

@BrianDillingham Thanks! I'll dive right into those.

@jlmmms no problem, start with the OO if its not clear so you don't get caught up on OO specifics in other videos and as a personal tip, try not to think of your specific task at hand, I've found these videos most helpful when my objective is learning general concepts rather than formally fitted solutions.

@jlmmns Start working on your application first, when you are done and happy about it, then begin creating your API.

I'm going to go against the grain of what everyone here is saying, simply because you're working with financial transactions.

Assuming you're going to need to do some reporting in the future, it's really handy to be able to query all transaction types at once and return a collection. E.g. the use case of "list all transactions for this user," or "get all invoices, credits and payments," if you're creating a statement of account.

Do you anticipate needing to record payments, credits or sales orders in the future? If so, it might be worth looking into single table inheritance and polymorphism for all of your basic transaction types, possibly with additional tables (or a key-value store, depending on the data you're capturing and query requirements) to store transactional data.

Thanks all.

@dfcowell Reporting sure is needed, yes. We're even tracking user actions and list important ones on our admin dashboard, like recurring invoices that were sent automatically.

We are now using a single Orders table for storing account upgrades, which are updated using postback transactions from our payment provider.

Currently, our database structure is basically nothing more than this:

Users
Invoices
Invoice_Products
Orders (account upgrades, using postback transactions from a payment provider)
...

I'm not sure if this is the best way of handling these things, but to me it's pretty straightforward. Maybe some suggestions concerning our database structure?

@jlmmns I'm working through this myself on a couple of different systems at the moment so I wouldn't call myself an expert, but here's a dump of my thought process looking at your problem:

I'd look at having a base transactions table which has all of the common columns a financial transaction needs on the main line:

• ID
• Transaction ID (readable if necessary, e.g. INV-001 for invoices, CRD-001 for credits)
• Transaction Date
• Posting Date
• Customer ID
• Type (this should be a fully-namespaced model classname as per STI principles)
• Total Amount (positive for CR transactions, negative for DR transactions)
• GL Impact Account ID (if necessary)
• Any domain-specific data which will be present on every transaction type (reference number, text memo field, job number, partner ID etc.)

All of your summary reports, customer statements etc. can be run against this table. Note that since you're using STI you may run into some issues enforcing strict constraints on a database level.

This is where you need to make a big decision - do you want to use a key-value store for record-specific fields (e.g. Due Date for Invoices, Location for Packing Slips or do you want to create a table for each transaction type which has a 1:1 relationship with the main Transactions table (Class Table Inheritance/CTI)?

The KVS is best if you aren't going to be querying or summarising your additional transaction fields. If you only have a couple of transaction-specific fields you need to query/summarise on, you might even find it worthwhile to promote them to the main line Transactions table and use a KVS for everything else. This can be a slippery slope though.

Class Table Inheritance makes it easy to query on all of your transaction-specific fields, while slightly complicating your model or repository code. Essentially, you need to load data from two tables to have a fully-populated model. I'd recommend replacing Eloquent with a data mapper like Doctrine if you go this route (it has native support for CTI,) as well as loading the data from the transaction-specific table only when necessary. I don't know if Doctrine supports this by default or not.

Essentially it means you'll have a main line Transactions table as above, but accompanying Invoices, Orders, Payments and Credits tables. Here's a simplified schema, note this is a contrived example and the fields aren't representative:

Transactions Table

• Transaction ID
• Transaction Date
• Transaction Type (your model class, in this case one of Invoice or Payment)
• Child ID (the ID of the related record, in Invoice ID or Payment ID respectively)
• Amount

Invoices Table

• Transaction ID
• Invoice ID
• Invoice Template
• Due Date

Payments Table

• Transaction ID
• Payment ID
• Payment Method
• Payment Provider
• Gateway Transaction Reference

Having to split out fields into multiple tables like this (and having essentially two records in two different tables for every transaction) is a little complex, but the ease at which you can run aggregate queries and summaries is worth it in my opinion. The value of being able to do something like Transactions::all() to build a customer statement should not be overlooked.

Anyway, happy to hear any feedback or other opinions, like I said, I'm working through this same problem at the moment.

---

Just remembered, the class structure would be something like this: an abstract Transaction class which has implicit instantiation logic for Invoice or Payment, based on the value of the Transaction Type column. E.g. if you had an Invoice with Transaction ID 1, Transaction::find(1) would return an instance of Invoice.

@dfcowell Thanks for your clear input! Very interesting to handle all Invoices, Quotes and Payments through one main Transactions table. It seems very efficient and orderly, especially when you need to extract statistics as an admin for example, or for individual users as well.

In the end, I want my tables to be as efficient and optimized as possible, not only for general performance, but since showing statistics is also a major feature of our application. And your method seems to make that easier, than just having only separate Invoices/Quotes/Payments tables.

Maybe the only downside I see to this, is that it's yet another table, which will fill rather quickly, when dealing with a lot of users. But I'm sure the benefits heavily outweigh the database footprint. I'm not experienced enough in database design to know these things... So I would very much like other people's input as well!