I want to delete a database record AND its child records. What is best for doing this?
SQL or Yii? Which do you think?
I want to delete a database record AND its child records. What is best for doing this?
SQL or Yii? Which do you think?
With sql you should must write sql query with AR it is easy. calling delete() or deleteAll(). If you want to delete referenced data and you are using AR you should override afterDelete method on your Model class. I think pure sql query is faster. 
Why bother remember that you should manually delete related records ? This can be a pain when you will have too many relations.
You should learn what a relational database is, therefore the advantages of using foreign keys and of course the HUGE advantage of using InnoDB as storage engine.
generally speaking it’s a good idea to only mark a record as deleted, instead of actually deleting it
but if you really want it gone, then either will do. personally, i go with cascading deletes directly in sql
Not everybody wants to define cascaded deletes in their schemas, as this can be dangerous.
Agreed.
Furthermore, it is relatively asinine to flat out say that InnoDB has HUGE advantages. It entirely depends on the table itself, as well as the primary purpose of the table. If the table is prone to more reads than writes, then MyISAM makes much more sense from a performance standpoint. If the table is almost always written to (for example an analytics tracking table) then InnoDB may be better.
No, they are not dangerous at all, as long as you know what are you doing.
I hate having orphaned content into a database, this is something that should be avoided with all costs.
From my point of view, MyISAM is behind InnoDB, maybe because i am only using InnoDB for 2 years or so, in this moment i couldn’t create a database using MyISAM, just because of the lack of the features Innodb has.
Sure, the only clear advantage for myisam is on full text searches, BUT this is all.
The count(*) issue in innodb is just a myth, having a where condition using a correct index is the same as MyISAM.
The select vs insert issue is the same, as long as the design of the database is crappy you will have problems on both, myisam/innodb. But, having the right indexes in the right places + using them correctly into your sql makes innodb become the first choice.
I see no use for myisam as long as innodb is out there, it simply makes no sense, if you want a relational database behaving as you wish to behave then is very simple, innodb and don’t look back, you have nothing to see there.
Instead of having a column with a "isDeleted" flag is it possible to copy the deleted record into another database table that is a mirror of the original automatically using SQL?
My guess is it would work something like …
Create mirror database tables
On Delete set a trigger to copy the db record from the original table into the mirror table
Is this possible? How would this be done?