PHP, deleting a category from unlimited category table

I would like to know what is the best way to achieve deleting a category (with all sub categories) from unlimited category table.

I was planing to go with;

• Delete the category and sub-categories.

• Since it is unlimited category system, in the end run another function to clear categories which has no parent_id records in the table.

  Is there any other, preferably开发者_JAVA技巧 more elegant way to achieve this?

Edit: My category table's structure

array(8) {
  ["id"]=>
  NULL
  ["parent_id"]=>
  NULL
  ["siteID"]=>
  NULL
  ["memberGroups"]=>
  NULL
  ["name"]=>
  NULL
  ["language"]=>
  NULL
  ["protectDelete"]=>
  NULL
  ["typeID"]=>
  NULL
}

Thank you for your time and concern.

If the concern is in deleting a record upon which many other records have a referential constraint, perhaps a strict delete isn't the right approach.

Instead, maybe try a "soft delete" where instead of deleting the record you'd set some flag on the record (such as an IsActive bit/tinyint field being set to false or 0). The logic surrounding the database would then need to know to filter on this field. The filtering is particularly easy if all of the data access is done through stored procedures or through a central data access layer in code, because the filter can then be transparent to the rest of the application.

It's a little more logic/work, but note the added benefits here:

• Deletes are very fast, regardless of referential constraints on child records
• Deletes are not permanent and are very easily recoverable
• Deletes don't necessarily have to cascade (child records can still be active and orphaned, depending on the logic for those particular entities)