Lately, I have been working with a lot of legacy SQL and this has caused me to think about what makes SQL (and database designs) good and bad.
There is a concept called ACID, defined within ISO/IEC 10026-1:1992. ACID is an acronym, that stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity is the idea of “all-or-nothing” transactions. In a transaction involving two or more discrete pieces of information, you want everything to save or nothing to save. Envision a transaction, where you are saving a new user. You might need to save account access (ie subscription details) information, account authentication information (ie a username and password hash), and billing information. If there is an issue saving any of those three pieces of information, do you really want the remaining data to successfully save? Over the past few years, I have been using MS SQL for work. One of my complaints about it is that MS SQL auto-commits transactions. You can use stored procedures to adapt, though.
Consistency simply means that you either save valid data or your don’t save anything. In ColdFusion, you can aid consistency with something like cfqueryparam.
Isolation means that a transaction which has not yet been committed must remain isolated from any other transaction. If there are two transactions executed at the same time, the second transaction must wait until the first one completes execution. Isolation is not necessarily easy. In high-traffic systems, there might be millions of transactions occurring at a time. This also becomes difficult when you are dealing with distributed RDBMSs.
Durability means that anything that is committed to the database will be available. This is achieved through good database backups and transaction logs.
There are more issues that need attention, though. Your top priority when designing any system is maintainability. This applies to the database schema, the database transaction, and the system that is consuming the information. If your system breaks and can not be fixed, what good is it? Your second goal should be to actually meet functional requirements. Your final goal should be to write a transaction that performs efficiently.
Meet these seven goals and you can’t go wrong.