Your payment processor isn't going to know how much anything costs. You'll need some (server-side) validation logic if you don't want people buying new high-end smartphones for a penny. Which event invokes that logic is immaterial.

> Your payment processor isn't going to know how much anything costs.

So?

> You'll need some (server-side) validation logic if you don't want people buying new high-end smartphones for a penny.

That validation logic should be expressed in SQL. It's really really simple. If inventory id and price match in your inventory table, insert into your transaction table.

Again, see my original point. PHP is just the glue for the database.

Hmm. Not to nitpick, but what if:

- you're running a promotion

- the price changed in between the time the person added it to their shopping cart and checkout

- two people are processed for a payment on the last item in inventory at the same time

- there are different options on the item, e.g. color, that change the price

- other people bought the last item in inventory already, but there's an equivalent product at the same price

- the person bought the product using a combination of real money and gift card money, which the payment processor does not handle

- the person bought other things in the same transaction, e.g. a gift card, that do not have an inventory ID

Those are just a few cases off the top of my head, but the short end of it is that expressing all of this with a combination of SQL and client-side logic is just madness. If you're saying that we don't need to write business logic in PHP any more, I think you just don't work with the same constraints that most businesses do.

All of these, save for the payment processor one, can and should be done in sql.

I think you are uncomfortable with SQL.

Ultimately it is the designer's choice where the business rules are handled...but to abstract those rules to levels above where the 'business' actually happens seems like a poor practice.

'Business' does not just happen within the database! You will have to perform logic based on events that occur elsewhere, e.g. on the network or within outside data sources! And to say that something should be done in SQL just because it can is patently absurd--do you know how much performance suffers as soon as you start roping in subqueries and calculated values? Half of the things I listed would require stepping outside of indexed columns in awkward ways--that was kind of my point. Good luck keeping that performant past a few million rows.

In fact, I would love to see you design a query that branches on the condition that the item is out of stock and automatically returns the top 3 equivalent parts for each brand using a junction table containing equivalencies, ordered by a column in the junction table containing the closeness of fit. Go ahead, humor me. Then try to pretend that that query will ever stand up under scale.

OK, I understand your point a bit better now. You're basically talking about writing the backend in almost-pure SQL.

The main drawbacks I can see to that are:

1) Most developers couldn't write enough SQL to escape a paper bag. That's fine; usually, you don't need to escape a paper bag with SQL. The problem is that this only works for people who are very comfortable with SQL. I'm decent with it, and perhaps I'll lean a bit more that way for my next project, but I'm not seeing most people doing this.

It's also not quite as simple as you say, because multiple items are typically combined into one. This is many:many, so you'll need a table to cross-reference them.

So, you'll need to have a transaction table and a subtransaction table. The subtransaction table can match against inventory, and the transaction table can sum the subtransactions to make sure the total is correct. This gets a little hairy around foreign keys, but it's pretty easy to code around.

2) This example is fine, but for any transaction more complex than this, you risk tying yourself to a particular DB backend. That's probably OK, as the DB backend contains most of your app code: it's like tying yourself to Python on the backend. It's just something to be aware of.

3) This is likely to result in a large number of transaction rollbacks, due to price changes and whatnot. This might be OK, or it might trip anti-fraud features on your payment processor.

> Most developers couldn't write enough SQL to escape a paper bag.

Agreed, but you can split your SQL queries into really simple ones and glue them with PHP. What I don't see anymore in my code is a hundred lines of unbroken PHP. Actually, I don't even see a dozen. A few lines, SQL, a few lines, SQL ... that sort of thing. That's what I'm talking about.

You don't need to be puritanical, although of course, you can. That's personal choice.