Let's say hypothetically an Account has 3 Contacts related to it. Each Contact can have a status of 1 through 5. If the sales reps are doing what they should, that status should always go up, but I want to proof it against accidentally saving with a higher status and then lowering. I want the Account to show the highest of Contact Status across all the contacts that belong to it. Let's call this hypothetically Account_Contact_Highest_Status.
What I've done so far is created a trigger after update/insert on Contact that ratchets Account_Contact_Highest_Status. I've bulkified it and it works fine. The problem is if somebody accidentally saves a contact with a higher status, ratchets the Account_Contact_Highest_Status up, and then re-saves the contact with a lower status.
I have a way to account for this, but it would require DML queries to get the status for all the Contacts on every Account involved. I can't think of a way to go about doing this that would be more efficient that would not violate my governor limits. Is there a better way, either via Trigger or other?