views:

65

answers:

4

I am a newbie. I am writing an application in Java which connects to Oracle 9i database. I need to enforce some rules in my application like:

Chair of Committee A must be added/deleted as a Member in Committee B.

There are around 10 such rules that need to be enforced. I am in a dilemma whether to enforce these rules by writing triggers or by programming within my application.

I appreciate any suggestions.

Thanks,

+3  A: 

I may be mistaken, but what you are talking about sounds like an actual business rule (Chairman of Comittee A must be a member of B, or cannot be a member of B). IMO, that should be handled by the application and not the database.

peacedog
+1 for business logic in the code. Personally I think triggers are evil for the vast majority of purposes.
Dave Swersky
@Dave Swersky: We use triggers to maintain audit trails in a very important data entry app (so all data entry changes are entered into a database table), and that's it. All those triggers are doing, though, is inserting a record in a table (indicating the value changea and the type of operation, as well as whodunit). I definately do not want my triggers changing data in other tables.
peacedog
+1  A: 

Rules that specifically address data integrity get enforced in the database. Rules that are more logical/business rules I don't think belong in the db, because

  • they're invisible to the application code
  • they're more likely to be mutable, and this can be inconvenient if they're baked into the db.

The type of thing I'd more likely expect to see enforced by the DB would be things like "Committee record name can't be null", i.e. things that are necessary for data integrity.

Steve B.
A: 

Assuming these are rules that relate to a policy of the organization in question - i.e. "all committee chairs must be a member of at least one other committee", or "no committee must have less than two members", then you should probably put them in Java. Java is a more flexible programming environment than SQL and an RDBMS.

Say your rule is "no committee chairman can be a member of any other committee". You might implement it like this:

public class Committee {

    private Person chairman;
    private Set<Person> members;

    public Person getChairman() {
        return chairman;
    }

    public void setChairman(Person person) {
        for (Committee other : person.getMemberships() {
            other.removeMember(person);
        }
        chairman = person;
        addMember(person);
    }

    public void addMember(Person person) {
        members.add(person);
        person.addMembership(this);
    }

    public void removeMember(Person person) {
        members.remove(person);
        person.removeMembership(this);
    }

    public Set<Person> getMembers() {
        return new HashSet<Person>(members);
    }
}

public class Person {

    private Set<Committee> memberships = new HashSet<Committee>();

    public Set<Committee> getMemberships() {
        return new HashSet<Committee>(memberships);  // Return a copy, not the original
    }

    public void addMembership(Committee committee) {
        memberships.add(committee);
    }

    public void removeMembership(Committee committee) {
        memberships.remove(committee);
    }

}

You could then write unit tests for Committee to verify that the rules for chairmanship are working, and they would run very fast, because they wouldn't need the database.

public class CommitteeTest {

    @Test
    public void testChairmanship() {

        // Set up the initial conditions
        Person person = new Person();
        Committee other = new Committee();
        Commiteee commitee = new Committee();
        other.addMember(person);

        committee.setChairman(person);
        assertTrue(committee.getChairman().equals(person));
        assertFalse(other.getMembers().contains(person));
    }
}

(You might want to use a mock object for the Person, since this unit test is supposed to test only Committee.)

As you add more rules, you would add more unit tests, and then would continue to run fast.

Ladlestein
Bear in mind that these 'enforcements' often break when multiple people are working concurrently. For example, someone adding a person to a committee at the same time as someone else makes them a chairman. The only way to really enforce such rules is to serialize the operations so that any validation test is done with the certainty that the state is fixed for the duration of the test.
Gary
+2  A: 

I think that if a constraint can be enforced in the database then it should be, however the application should try to enforce it first and the database should be there only as a backup to that -- a fail-safe, if you like.

However there are some constraints that are difficult to enforce in an RDBMS and this example is one of them. It's difficult because the database's native constraint types (unique, check, references etc) do not allow you to enforce it and you would have to revert to a trigger or materialized view or somesuch. Triggers are terrible at enforcing constraints because query isolation levels can cause some violations to slip by, and they also cannot read or modify the affected table (the mutating table problem).

to be fair though application-level constraint checking suffers from the same problem with query isolation levels, hence the use of database constraints as a backup.

David Aldridge
I agree. Any business rule should be as low down in the chain as possible. If you enforce a business rule in the database then when you re-write your client or write your extra iPhone widget wonder then the business rule will still be there. However, if you find yourself jumping through unnatural hoops to make triggers enforce your business rules then don't worry about it too much - it can just be an application-level business rule.
Nick Pierpoint