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.
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.