Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The problem:

  • I have a table with rows, nothing special about it
  • When a database user removes a row, it should increment a number
  • A database user is only allowed to remove 3 rows max in the table
  • A database administrator should be able to add more "remove tokens" to a database user

I know I should have a BEFORE DELETE trigger to see if the user can remove a row, and an AFTER DELETE trigger to increase the number of rows deleted by the user.

How should I store the info about how many rows a database user has removed from that specific table, and how do I allow a database admin to add more "remove tokens" to the user.

I thought of creating a table to store this, but I wonder if there are different ways to do it without a table? This might sound like a weird question, but I'm just trying to figure out how more advanced stuff works.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

With this kind of granularity, you need to have a rather elaborate setup.

I was thinking of suggesting several things

SUGGESTION #1 : Use Stored Procedure for Specialized DML

Having to micromanage the number of DELETEs per user to mitigate DML should not become a server-centric function for DELETEs against a specific table. That should remain an application-centric function. Therefore, create a Stored Procedure that can manage the number of DELETEs.

SUGGESTION #2 : Use the BLACKHOLE storage engine

Any audit trail of DELETEs using a real table can create a localized disk bottleneck that would be a little clumsy to throttle. If the audit trail was recorded in a BLACKHOLE table, that would significantly reduce any disk I/O issues that would otherwise rear its ugly. If you write to a BLACKHOLE table, where would the audit trail data be written? That's leads up to...

SUGGESTION #3 : Use MySQL Replication

How would MySQL Replication help? Suppose the table is called mydb.audittrail. If the mydb.audittrail uses BLACKHOLE on the Prod Server, it could replicate to a Slave that that has mydb.audittrail as MyISAM. That would be the location of the actual audit data. The only drawback would be added disk I/O for writing audit trail info to the binary logs. You can further reduce that by having the binary logs stored on SSD or a RAM disk.

Please see my answer to Is it normal to use many Triggers? for further info on this concept of audit trail recording with the BLACKHOLE Storage Engine and MySQL Replication.

share|improve this answer
    
Thanks for the info, I'll gonna look into it and chose what I think fits me best right now. Disk I/O right now isn't a problem, it isn't any production database, The database would only contain a couple of tables and some dozen rows, and I'm only doing this to learn, but yeah it's good to keep in mind when doing something real. –  galaxyAbstractor Sep 11 '12 at 13:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.