Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

FHoulbreque's avatar

Accounting project and migration from plain PHP app

Hello there,

I did get back the development of an existing accounting PHP application. Currently, every customer, every new year, get a new app set up... The way things are handled is a new database for the new company/year is created, the whole schemas duplicated, and a new vhost is created, with a few environment variable to give the app the right database connection info. Pretty straight forward.

The fact is now, we want to migrate this application in both term of coding (the original developer did an horrible job with code duplication, functional development, php and html mixed everywhere, very heavy assets (CSS and JS), etc...

We want to start anew and I opted for Laravel, Livewire, TailwindCSS and Alpine as my base set. The application isn't complex but we want to change things up :

  • Have a centralized entry point, thus users account being stored in only one table, main Laravel user table, so the admin may access any company/year datas from one point.
  • Keep the data of every year of accounting and other function separated one of another, but the structure of said data will stay the same.

I have multiple ways I can handle this but mainly my issue is :

  • Should I keep a database per year and per customer. Which means I need to dynamically collect the Models to the right Database depending on the user or the choice of the admin
  • Should I have a database for every customer and tables set for every year with prefix per year.
  • Should I have one database for everything with a set of tables for every customer and each year with a prefix for every company and year combination (may amount to the hundreds, not the best way to handle things).

In any case, would like your advice or you share your experience with this kind of problem on:

  • How to dynamically link models to these database/table combination as for every customer/year the structure will be replicated. Can I use a middleware to set up the secondary connection correctly, or should I use the session to keep the selected year or company, etc...
  • How to handle future database migration on the first opening of said set of tables if I need to make changes later on.
  • Any issue you may see come to the front.

I thank you for your advices on this, as this community has always been helpful.

0 likes
15 replies
martinbean's avatar

@fhoulbreque If this is migrating an existing vanilla PHP application to a framework like Laravel, then I’d just concentrate on migrating your application “as is” to Laravel first, before then adding “niceties” such as Livewire, Tailwind, Alpine, etc. The reason being, you want your application in a working state and not half-migrated to Laravel, half-using Livewire, half-using Tailwind, etc. You want confidence when re-platforming, especially when dealing with something as sensitive as accounts and financial data.

I’d tackle this by:

  1. Creating a new Laravel application.
  2. Renaming public/index.php to public/laravel-index.php or something.
  3. Dump your existing, vanilla PHP application inside the public directory.
  4. Updating the .htaccess file if using Apache, or your nginx site configuration, to check to pass any unhandled requests to your public/laravel-index.php file.

With the above, when a request comes in, what should happen is:

  • Apache/nginx will first check the public directory for a file matching the request (i.e. https://example.com/some-old-script.php).
  • If the file exists, it’ll be executed.
  • If it doesn’t exist, the request will be passed to Laravel for handling. Laravel will then either successfully route the request, or throw a 404 if there is no matching route.

This means that you can work on re-platforming your application to Laravel piece by piece. Take a script, create a controller for it, delete the original script when you’re happy it’s working when being served by Laravel. Your public directory should slowly empty of “old” code, and your Laravel application should start growing with code converting from your vanilla PHP scripts to code in controllers.

As for the database set-up, it’s definitely unconventional. You’re basically describing a multi-tenanted application. And whilst one flavour of multi-tenancy sees a database per tenant, it’s odd to create a completely new database for each financial year as well. I don’t know how many customers you have, or how long the application has been running for, but that’s obviously going to lead to a lot of database.

Whether you go the database-per-tenant route, or a single database for all tenants and financial years, may be more a legislative choice to make than a technical one. You may need to check with your country’s laws on storing financial data, and whether it needs to be segregated from other customers’ financial data or not. It may also give your customers piece of mind knowing their data is walled away from other customers’. Nothing will ruin confidence in your application more than a customer logging in, and accidentally seeing other customers’ data due to a bug or misconfiguration.

FHoulbreque's avatar

@martinbean This approach is what I wanted at first but sadly, I can only say there's almost nothing to salvage from the current application. I had to solve issues and have always a few to solve as it's buggy, messy, not optimised at all. I think the "developer" that created this thing didn't know anything about what function is... He never heard of MVC model, nor he knew about UNION or JOIN queries. I tried many time to progressively migrate things but it takes even longer than starting anew. The HTML / CSS side is even worse.

About the number of databases, you are right, they are numerous and it's a real pain to manage. The segregation of data is required by law for corporation only and doesn't apply to our customer but this will change soon, so if we want to get a few government certification later on, it's better to plan ahead. The fact is I don't know if I want to do it per customer basis or per year and customer. I ruled out the global database anyway as It would have a lot of tables and wouldn't be easier to manage than multi-table.

Thank you for your suggestion, even if I already explored this and had to go the "start anew" way.

I would happily get any suggestion about multi-tenancy with Laravel and other question I asked before. Especially the model/table dynamic binding.

martinbean's avatar

@FHoulbreque The problem with just “starting over” is, there’s nothing to stop you from introducing entirely new bugs, and problems stemming from the new implementation that weren’t present in the old implementation.

Yes, what you have may be “bad” code. But you remove the bad code as and when you convert it to “good” code. Eventually you will be left where you want to be: a Laravel application doing the same functions as the “old” code with none of the old code left.

krisi_gjika's avatar

does you application need to load data for different years at the same time, or is all functionality scoped to one year? Example: comparing profits of this year to the last year.

FHoulbreque's avatar

@krisi_gjika Mostly no, there's only on the creation of a new year that you need go import data from the previous year, but no, all the standard function are scoped to one year at a time. The only common part of the website is the entry point.

jlrdw's avatar

I would first concern myself is the accounting correct, worry about pretty as a last thing.

When I do bookkeeping apps, I close out a year and it's backed up to another database.

I don't create a new database. But before doing anything learn the legalities involved in the accounting you are doing.

Also for this type of app, have a security audit performed.

FHoulbreque's avatar

@jlrdw My associate is a certified accountant, so there's no issue on this side. But thank you for the remark, it is indeed important. I am more concerned by the state certification I want to obtain, especially with the new laws regarding digital invoicing (data based only).

jlrdw's avatar

@FHoulbreque I suggest use the old software, meanwhile start developing new in laravel.

Then when ready do the switch.

There is nothing wrong with part of a year done in software A and another in software B.

Just my thoughts on it.

FHoulbreque's avatar

@jlrdw This was the plan since the start, I'm developping importers as well to ensure data are at the correct format after migration... My question was about multi-tenancy ideas, how to handle this as if the models are the same, the "target" table is different every time. I decided on a database per customer, so I may ensure data are segregated correctly and set of tables with year prefix in those databases.

Thank you for your advice anyway !

martinbean's avatar

@FHoulbreque But why are you prefixing tables in the first place? What happens if you want to find all invoices for a particular vendor? You’ve now got to search multiple tables. If your client has done business with a particular vendor for say, 20 years then you now have to search 20 tables.

FHoulbreque's avatar

@martinbean this is not an invoicing application but an accounting one... And the need of segregating data par year is to ensure conformity with the law... The invoicing part is not standard a well... This software is dedicated to employee organisations in medium sized business, managed by elected employees, etc... The invoicing part is mostly theater tickets sold at a discount. Or gift for the employees children, little money for the weddings. The invoicing part will not be "prefixed" but the accounting will.

jlrdw's avatar

@FHoulbreque

The invoicing part will not be "prefixed" but the accounting will.

An invoice falls under an account.

Business type applications is all I do, and the statement is confusing, sorry.

Just make a chart of accounts and do the transactions as needed.

And since each (if I understood) can apply to a separate account, i.e.

  • ACCT-1 bought a thing

  • ACCT-147 bought another something

Then enter the transaction for whichever account bought whatever, etc.

I don't even seen where there could be any question on how to deal with any of this, is just my opinion, sounds like basic bookkeeping.

FHoulbreque's avatar

@jlrdw it's just the way the application is designed. You have a ticket system which is fully independent from the accounting one. I would like to change that but customers don't want it. So to make it simple, the invoices aren't in the accounting part automatically. This is not standard, it make no sense, it's confusing for me as well, but it is what it is... I'm trying to strengthen the accounting part but the invoicing part is to stay fully independent.

But nobody answore my initial question yet.

jlrdw's avatar
jlrdw
2 years ago
Best Answer
Level 75

@FHoulbreque okay:

Have a centralized entry point, thus users account being stored in only one table, main Laravel user table, so the admin may access any company/year datas from one point.

Yes

Keep the data of every year of accounting and other function separated one of another, but the structure of said data will stay the same.

Backup year end to a backup database, and truncate most of old, carry over December not completed to new year.

Should I keep a database per year and per customer.

No not per customer, have one database. If you were using Sage or Quickbooks would you want 20 copies of accounting software?

Should I have a database for every customer and tables set for every year with prefix per year.

One accounting - bookkeeping application

How to handle future database migration on the first opening of said set of tables if I need to make changes later on.

Practice migrating warning backup EVERYTHING before messing around with migrations. A while back someone lost over 6 months of real company data here on the forum.

You said there were accountants, most of this should already be answered.

Any issue you may see come to the front.

None if you stick to regular bookkeeping practices.

Edit:

the invoices aren't in the accounting part automatically.

I suggest "call the ball" and let them know you are going to use standard bookkeeping practices.

1 like
FHoulbreque's avatar

@jlrdw Thank you for this.

As for the backup, I would never work on the production environment. Thanks for the reminder, I know of friends who did that. I never did myself. And some recent hosting issue makes me extremely careful. I also always put latest version in a semi-public test environment with a few chosen customers before deploying it globally.

I'll try to find the best way to handle things.

No not per customer, have one database. If you were using Sage or Quickbooks would you want 20 copies of accounting software?

I would point out that all the accounting software I went across in my country are always with different database for every fiscal year, and of course different for every company. The reason is it is required by law. This is the way it is currently designed and it's hard to maintain. No doubt about that.

I know I could store things in one table (for invoices I could do it per customer basis). I would really like to have all ledger entries in one table, and sorting them for every customer and fiscal year through queries, but I can't do that.

That's why I asked this question. So I can use one database but I'll have number of table into it, which is not really simple to manage. I can't really avoid multi-tenancy model. My question was more on the most efficient way to do it... in the same database, with multiple prefixed tables, with segregated databases or a mix of both. I'm sorry if I am not clear but that's what I was asking.

And in any case, my second interrogation was can I manage this easily dynamically. I mean if customer A connect, it link the models to the table with this customer's prefix. If it's customer B, etc... I never did multi-tenancy in Laravel environment, I know how I would implement that but I was asking for best practices there.

Please or to participate in this conversation.