http://qs321.pair.com?node_id=190309


in reply to Twin interfaces, and one and a half databases to a project

Allow me to comment from the DBA/SysAdmin side of the fence. You haven't specified an RDBMS, I'll use Informix as an example, since I know it well.

As DBA I have the ability to control down to the column level, what operations a user can perform on the database through database permissions. I can also define views that are subsets of a table or combinations of several different tables and control access to those. In a customer-input environment, the various CGI scripts connect to the database as users with various levels of priveleges. End users have the lowest, admins have the highest. I'm able to take advantage of the RDBMS's capabilities for locking and transaction integrity as the data is processed.

I've also just completed a medium sized (200+ tables) replications setup for disaster recovery. The most difficult thing to handle in replication is conflict resolution. If the same row is changed on both databases you run the risk of corrupting the data in one or both copies of the table. In many cases you are chasing a conflict after the transaction itself is completed. Informix replication works by "snooping" through the transaction log to find the transactions in the tables you have defined to replicate. Bottlenecks can occur that interfere with the replication process and lead to time intensive re-syncronization. In short, the replication process adds a layer of data management tasks to the app that can be very costly.

If it was me, I'd argue for controlling permissions at the CGI and the database level. I think those tools are better suited to the task at hand. The subset/replication scheme seems like "security through obscurity." Build the security into the database design and the code, and save the replication for disaster recovery.

Dave


Code On!