Should I enforce business logic through database errors?

Question

There's an interesting design decision I've been thinking about lately. Let's say I'm adding usernames to a table, and I want to make sure there are no duplicates. The username column is NOT NULL UNIQUE. I could either:

1. Query the database before inserting to make sure there are no duplicate names, or
2. Just INSERT, and catch any exceptions that come from the database engine.

Assuming the DB I'm using is capable of enforcing constraints, I was wondering what situations each of these choices is appropriate in.

Answer 1

Can you cache the username list and check it on the application side without going to the database? You should still have the unique constraint on the database to ensure no bad data gets in (always protect the data at the database level first and foremost) but if you can do the check from a cache, you could save a whole round trip to the database when someone selects the same username as an existing user. Now this may depend o nthe size of the data you would need to cache and how often the cache would have to be updated. Not knowing your system, I can't say if it is practical, but I would at least look into doing it.

Answer 2

Do you expect that the new username is likely to be unique? Or is it likely that it will be a duplicate? If the username is likely to be unique, doing the insert and catching the exception would be more efficient. If the username is likely to be a duplicate, it will be more efficient to check for duplicates (and potentially look for a similar but not yet taken username) rather than trying to catch the exception. Obviously different databases and different versions of those databases have a different breakeven point on the relative probabilities. But in general, if you're building a system for a company where everyone has a unique username anyway, do the insert and catch the exception. If you're building Hotmail, check for duplicates first.

A quick demo (on Oracle 11.2.0.1) shows that it's roughly 7 times as expensive to do an insert that fails and to handle the exception than it is to do a check before the insert and then write the data.

SQL> create table username_test (
  2    username varchar2(30) unique
  3  );

Table created.

SQL> set timing on;

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_cnt integer;
  3  begin
  4    for i in 1 .. 100000
  5    loop
  6      select count(*)
  7        into l_cnt
  8        from username_test
  9       where username = 'JCAVE';
 10      if( l_cnt = 0 )
 11      then
 12        insert into username_test( username )
 13          values( 'JCAVE' );
 14      end if;
 15    end loop;
 16* end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.20
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_cnt integer;
  3  begin
  4    for i in 1 .. 100000
  5    loop
  6      begin
  7       insert into username_test( username )
  8          values( 'JCAVE' );
  9      exception
 10        when dup_val_on_index then
 11          null;
 12      end;
 13    end loop;
 14* end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.58

Answer 3

It almost always seems like a good idea to do option 2. I wouldn't recommend option 1 because you've effectively doubled the amount of time required to do inserts (they all require reads first). Besides, some new developer is going to just commit sometime and not to the check, and it will get broken.

Another thing to consider is how much downtime is appropriate? Is this a mission critical app? What happens if the business logic is corrupt? Will factories shut down if it is? Or will it just be some annoying bugs.

You can't afford to have your factories shut down because some exception you didn't think of crashed your server. So, perhaps a nightly or weekly check on the data correctness can also help in this case. However, I feel the DB capabilities to enforce uniqueness (and potentially other enforcements) are the appropriate way to go.