views:

251

answers:

4

Hi all,

I am relatively new to PHP, but experienced Java programmer in complex enterprise environments with SOA architecture and multitier applications. There, we'd normally implement business applications with business logic on the middle tier.

I am programming an alternative currency system, which should be easy deployable and customizable by individuals and communities; it will be open source. That's why php/mysql seems the best choice for me.

Users have accounts, and they get a balance. also, the system calculates prices depending on total services delivered and total available assets.

This means, on a purchase a series of calculations happen; the balance and the totals get updated; these are derived figures, something normally not put into a database.

Nevertheless, I resorted to putting triggers and stored procedures into the db, so that in the php code none of these updates are made.

What do people think? Is that a good approach? My experience suggests to me that this is not the best solution, and prompts me to implement a middle tier. However, I would not even know how to do that. On the other hand, what I have so far with store procs seems to me the most appropriate.

I hope I made my question clear. All comments appreciated. There might not be a "perfect" solution.

A: 

There is absolutely nothing wrong with using triggers and stored procedures and other features that are provided by your DB server. It works and works well, you are using the full potential of the DB, instead of simply relegating it to being a simplistic data store.

However, I'm sure that for every developer on here who agrees with you (and me), there are at least as many who think the exact opposite and have had good experiences with doing that.

Anti Veeranna
+1  A: 

As is the tendency these days, getting away from the DB is generally a good thing. You get easier version control and you get to work in just one language. More than that, I feel that stored procedures are a hard way to go. On the other hand, if you like that stuff and you feel comfortable with SPs in MySql, they're not bad, but my feeling has always been that they're harder to debug and harder to handle.

On the triggers issue, I'm not sure whether that's necessary for your app. Since the events that trigger the calculations are invoked by the user, those things can happen in PHP, even if the user is redirected to a "waiting" page or another page in the meantime. Obviously, true triggers can only be done on the DB level, but you could use a daemon thread that runs a PHP script every X seconds... Avoid this at all costs and try to get the event to trigger from the user side.

All of this said, I wanted to plug my favorite solution for the data access layer on PHP: Doctrine. It's not perfect, but PHP being what it is, it's good enough. Does most of what you want, and keeps you working with objects instead of database procedures and so forth.

Regarding your title, multiple tiers are, in PHP, totally doable, but you have to do them and respect them. PHP code can call other PHP code, and it is now (5.2+) nicely OO and all that. Do make sure to ignore the fact that a lot of PHP code you'll see around is total crap and does not even use methods, let alone tiers, and decent OO modelling. It's all possible if you want to do it, including doing your own (or using an existing) MVC solution.

Yar
Btw, using stored procedures does not necessarily exclude having source control, on the contrary, you can and should put ALL your code into source control.
Anti Veeranna
True Anti, anything can be version controlled, but PHP code is particularly amenable. MySql SPs are stuck inside the DB, right?
Yar
No, you keep the SP source code in files (and therefore in version control) and then load them to DB on deployment. Easy :)
Anti Veeranna
Okay, thanks, I'll alter my answer slightly.
Yar
A: 

Thanks guys.

I was using db triggers because I thought it might be easier to control transaction integrity like that. As you might realize, I am a developer who is also trying to get grip of the db knowledge.

Now, I see there is the solution to spread the php code on multiple tiers, not only logically but also physically by deploying on different servers.

However, at this stage of development, I think I'll stick to my triggers/sp solution, as that doesn't feel to be that wrong. Distributing on multiple layers would require me to redesign my app consistently.

Also, thinking open source, if someone likes the alternative money system, it might be easier for people to just change layout for their requirements, while I would not need to worry that calculations get wrong if people touch php code.

On the other hand, of course, I agree that db stuff might get very hard to debug.

The DB init scripts are in source control, as are the php files :)

Thanks again

fablife
+1  A: 

One issue with pushing lots of features to the DB level, instead of a data abstraction layer, is that you get locked into the DBMS's feature set. Open source software is often written so that it can be used with different DBs (certainly not always). It's possible that down the road you will want to make it easy to port to postgres or some other DBMS. Using lots of MySQL specific features now will make that harder.

acrosman