CODEWITHSUNDEEP
phpmysqlrelational-database
John
John

Reputation: 465

Sql summary tables or dynamic aggregation

I have an applicaion built with php mysql that handles invoicing of subcontractors for my company.

My question is this.

Lets say I have 3 different types of invoices which are all calculated(totaled) differently depending on type. Also the type of total returned differs depending on the privilege of the authorized user.

So I am trying to debate the best way to handle it:

I could calculate the invoices using sql which I *think would be the quickest, but would require me to use business logic inside sql.

I could pull everything down in one result set and then use php to map and calculate the invoices at run time,

Or I am leaning toward using different summary tables, which are created/updated when and invoice is created/updated, and the creating views in mysql to control how they are returned. Im thinking this would be best but im not sure. It would cause the mapping process to become more difficult but it would allow for all business logic to remain in my php classes and my sql selects would be simplified.

So before I commit to anything I just wanted some other input.

Thanks John

Upvotes: 0

Views: 199

Answers (1)

Ben Lee
Ben Lee

Reputation: 53359

Don't put business logic in MySQL queries when it can be avoided without taking a performance hit.

I'd start with the simplest method possible, in this case "pull everything down in one result set and then use php to map and calculate the invoices at run time". Chances are pretty good that for a relatively low-volume database (like a subcontractor invoice database), any method is fast enough that optimization may not be necessary at all.

If you find the requests to be too slow, or if the on-the-fly data crunching becomes a bottleneck of some sort, then go with the "summary tables" solution (assuming this is a method that allows speed while still keeping the business logic in the app code).

It's okay to put business logic in MySQL if it's really the only way to make a query fast. But generally speaking, avoid this.

Upvotes: 1

Related Questions