database design - how do you use the database to confirm complete data entry and validation

Question

i have an Applications table and a Resources tables. Since many resources can work on a single application and many applications can be worked on by a single resource, i created a 3rd table:

ApplicationResources:
- ID (int)
- ApplicationID (int)
- ResourceID (int)
- PctOfTime (float)

is there anyway in the database itself that i can ensure that the total of all resources's PctOfTime added up to 100%?

Answer 1

There's no declarative way to do that in SQL.

You can accomplish this using a trigger (a stored procedure that is run whenever a row in the ApplicationResources table is INSERTed, UPDATEd or DELETEd. The trigger can test the condition and reject the change if it would cause the condition to be false.

The problem is that you might need to allow the table to be briefly invalid. For instance, if you DELETE a record for a resource, the condition will not be true until you add or update one or more records to get back to 100%. In order to get that to work, you'd need to defer your trigger until the end of a transaction and the ability to do that varies among databases.

Your problem is easier to deal with if you want the condition to be that no resource can ever be more than 100% allocated (and that allows for resources that are not fully utilized as well).

Answer 2

Do you use Oracle?

If you use Oracle you can validate the data in a declarative way with the use of a fast refresh materialized view, you have to add a check constraint to this materialized view. This means that the validation will be done when a commit is happening.

Edit1

It saddens me a little that oo didn't anwer my imho really very simple question, so I don't know whether or not he/she uses Oracle, but anyway:

Let's create a table with a materialized view log, a materialized view with a group by on resourceid and a check constraint to check that the sum of PctOfTime equals 100 for every resourceid.

create table applicationresources 
(
id number(10) not null primary key,
applicationid number(10) not null,
resourceid number(10) not null,
pctoftime  number(3)  not null
);

create materialized view log on applicationresources 
with rowid (id,applicationid,resourceid,pctoftime) including new values;

create materialized view pctoftimecheck
refresh fast on commit
as
select sum(pctoftime) sum_time,resourceid
from   applicationresources
group by resourceid
/

alter table pctoftimecheck add constraint c1 check(sum_time=100);

Now two examples:

Example1 (will succeed):

insert into applicationresources values (1, 1, 1, 50);

insert into applicationresources values (2, 2, 1, 50);

commit;

Example2 (will fail on commit because sum=101)

insert into applicationresources values (3, 1, 2, 50);

insert into applicationresources values (4, 2, 2, 50);

insert into applicationresources values (5, 3, 2, 1);

commit;

SQL> Error: ORA-02290: CHECK-constraint (DSEDD.C1) violated.

See for more info: http://rwijk.blogspot.com/2009/06/fast-refreshable-materialized-view.html

Answer 3

This can be accomplished with a trigger, but the problem is deifng what to do when the total does not equal 100%. If I change one value from 50% to 60% (And I already havea nother record for the other 50%) do I want to adjust the other record(s) down to get to 100 or reject the update. Also there is the problem of the initail insert of records. THe first record is 60% but under your rule I would have to enter it at 100 % until I enter another record. Finally what to do if the total of all the records does not add up to 100%. Do you split the differnce among all the records, split the differnce between the unchanged records keep the changed value or reject the change that is making the totals not add up?

THis is a trigger that will have to be very carefully thought out and designed.