There are always two ways to solve everything: the easy way and the hard way. Time and time again, I see people choosing the hard way. It is not always done consciously. More often, it is done out of ignorance. They never expected the database to be able to do “that.” I, on the other hand, expect the database to be capable of anything and only do it the hard way (by writing it myself) when I discover it can’t do something.
For example, I am frequently asked, “How can I make sure the end user has only one session in the database?” (There are hundreds of other examples I could have used here.) This must be a requirement of many applications, but none I’ve ever worked on—I’ve not found a good reason for limiting people in this way. However, people want to do it, and when they do, they usually do it the hard way. For example, they will have a batch job run by the operating system that will look at the V$SESSION table and arbitrarily kill sessions of users who have more than one session. Alternatively, they will create their own tables and have the application insert a row when a user logs in and remove the row when they log out. This implementation invariably leads to lots of calls to the help desk because when the application crashes, the row never gets removed. I’ve seen lots of other “creative” ways to do this, but none is as easy as this:
$ sqlplus eoda/foo@PDB1
SQL> create profile one_session limit sessions_per_user 1; Profile created.
SQL> alter user scott profile one_session; User altered.
Now we’ll try to connect to SCOTT twice; the second attempt should fail:
SQL> connect scott/tiger@PDB1 Connected.
SQL> host sqlplus scott/tiger@PDB1
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
That’s it—now any user with the ONE_SESSION profile can log on only once. When I bring up this solution, I can usually hear the smacking of a hand on the forehead followed by the statement “I never knew it could do that.” Taking the time to familiarize yourself with what the tools you have to work with are capable of doing can save you lots of time and energy in your development efforts.
The same “keep it simple” argument applies at the broader architecture level. I would urge people to think carefully before adopting very complex implementations. The more moving parts you have in your system, the more things you have that can go wrong, and tracking down exactly where that error is occurring in an overly complex architecture is not easy. It may be really “cool” to implement using umpteen tiers, but it’s not the right choice if a simple stored procedure can do it better, faster, and with less resources.
I’ve seen projects where application development has been going on for months, with no end in sight. The developers are using the latest and greatest technologies and languages, but development is not going very fast. It wasn’t that big of an application— and perhaps that was the problem. If you are building a doghouse (a small woodworking job), you wouldn’t bring in the heavy machinery. You’d use a few small power tools, but you wouldn’t have any use for the “big stuff.” On the other hand, if you were building an apartment complex, you’d have a cast of hundreds working on the project; you’d have the big machines—you’d use totally different tools to approach this problem. The same is true of application development. There is not a single “perfect architecture.” There is not a single “perfect language.” There is not one single “perfect approach.”
For example, to build my website, I used APEX (Application Express). My website is a smallish application; there was a single developer (or two) working on it. It has maybe 20 screens. PL/SQL and APEX were the correct choice for this implementation—it did not need a cast of dozens, coding in Java, making EJBs, using Hibernate, and so on. It was a simple problem, solved simply. There are a few complex, large-scale, huge applications(we buy most of those today: our HR systems, our ERP systems, and so on), but there are thousands of small applications. We need to use the proper approach and tools for the job.
I will always go with the simplest architecture that solves the problem completely over a complex one any day. The payback can be enormous. Every technology has its place. Not every problem is a nail, so we can use more than a hammer in our toolbox.
