My brother is having a look at the database now. He's been creating commercial database systems since the 1970s and currently gets flown around to create/update/repair payroll and inventory systems for some of the largest companies in the Province.
He's hooked on the game and wants to see if he can make the database easier to maintain. I had a chat with him last night and he too isn't a huge fan of cascading deletes (though in some instances he says it just makes too much sense not to). He prefers external utilities and/or stored procedures.
Last night he was just getting familiar with MySQL's syntax (he's mostly Oracle and MSSQL) and wants to peruse the database for orphaned rows etc.
Another thing we talked about was index numbers. I described how most tables use the zone number as the basis for row ID to make it easier for us humans to figure out what zone the record is for. I also talked about the idea of reserving the initial set of IDs for the main database developer (e.g. peq devs use 3000-3999 for zone 30) and add-on people would use a higher numbered system. Say we leave 1000 zones for the origninal database devs (ID 0 to ID 100999) and add-on devs would start at 110000.
One issue with existing utilities is that when they create a new index number they typically just get the highest index number and add 1. While this is safe, it doesn't follow the convention of using the zone number nor does it make use of gaps in the index sequence caused by deletes. Say I made 25 spawns in zone 113000 and deleted spawn # 12. If a new spawn is added, it should choose the lowest free ID, #12 and not #26. 1000 spawn IDs might seem like a lot but if you edit the zone semi-frequently, deleting old spawns and creating new ones, it wouldn't take long to run out of IDs if you didn't go back and re-use the "free" IDs.
Although I know about transactions (*safe* database operations that can be rolled back if you don't like the results) I've never actually used them. I'm assuming my brother would ONLY use transactions, showing the user the results before the user commits to the changes. I'm going to have to get him to show me how that works
Anyway, the first thing he's going to do is figure out the table relationships then he's going to start writing utilities to check for and clean up orphans. After that he'll start looking at simplifying deletes and creating new IDs within the numbering convention.
Of course, in a couple weeks he has to fly off to Radium or some other logging town and work on someone's payroll system, so don't expect tons of stuff to be posted in the next month.