I started using CakePHP and theres a whole lot of new stuff to learn. I have a particularly hard time to with dealing with relational databases and stuff like that. My categories databases are nested tree heirarchies and while cake has functionality to deal with that, I don’t know how to use it yet. So an alternative way would be to setup an SQL on INSERT trigger that automatically rearranges the lft and rgt fields when I insert a new entry. Is this is a reasonable way to do it, or should I just find out how to do it with PHP? My PHP applications get pretty bulky and packed full of code sometimes so its nice to have some of the functions stored in the database instead.
I don’t think there is anything unreasonable with moving the logic to manipulate the nested set to the database instead of application code. You could even make a stored procedure for the specific application level tasks that require manipulating the hierarchy. That would probably be more appropriate than triggers. With triggers you might run into some issues considering you will need to change the table which the trigger is attached. I believe you can get around that with running a trigger after insert/update but a stored procedure seems more appropriate if you would like to move the business logic into the database instead of application code. Though at the same time if you say cake has tools to deal with this than perhaps it is best to learn and use those tools instead of fighting them unless there is a problem with them.
I haven’t used triggers in years. I prefer these types of things in the application so you can change the behavior easily and choose when they execute.
Cheers. I’ve been focusing on PHP for years and neglected learning about SQL so all this stuff is new to me. First time I hear about “stored procedures” but I’m guessing its something like SQL functions that run when a particular event is triggered. One of my sites has a complicated database system (the relational tables are difficult to work with using cakephp) so it would be really helpful if I could make SQL run some commands whenever SELECT is run on a particular table. Cakephp is great, but I need to get things up and running properly before I can dedicate time to learning it.
Moving some of the application logic to triggers is not a bad idea but the question is what database are you using? Triggers in MySQL are poorly done yet so I find it a bit clumsy to program and use them. Also, many shared hosts don’t allow triggers so if you want your code to be portable then it’s not a good idea. Another thing to consider is server load - quite often the database is the part that eats up most of the resources so in that case it’s a good idea to do as much logic in PHP as possible to offload the db - but this might not be a problem in your case.
I think moving a lot of the business logic to triggers and stored procedures is most useful in cases where multiple clients (possibly working in different environments/languages) connect to the db so all your important stuff is centralized. In case of most PHP applications you have only one client - PHP - so this point is not really that important.
Generally, MySQL’s SQL is a pretty basic language compared to PHP so at times you may run into limitations which will make it much harder to do the stuff you want in triggers or stored procedures. If I were to go that route I’d choose a more advanced db and preferably also have the ability to write stored procedures in languages other than SQL for best flexibility and performance. But for general work in PHP + MySQL I stay away from triggers, keep most of my code in PHP and occasionally I use stored functions, procedures and views for simple stuff that the db is well suited for.