oracle (or any relational) data model question. Parent with fixed number of Childeren?

Question

This is a particular problem that I have come across many times, but I have never really found a simple solution to this (seemingly) simple problem.

How to you ensure that a given parent has a fixed number of children?

1) Example.

How do you make sure a given class has only , say, 50 students enrolled..?

create table class(
  class_id number primary key,
  class_name varchar2(50),
  class_attributes varchar2(50)
);

create table student(
    student_id number primary key,
    student_name varchar2(50),
    student_attributes varchar2(50)
);

create table class_student_asc(
    class_id number,
    student_id number,
    other_attributes varchar2(50),
    constraint pk_class_student_asc primary key (class_id,student_id),
    constraint fk_class_id foreign key (class_id) references class(class_id),
    constraint fk_student_id foreign key (student_id) references student(student_id)
);

These are the implementations that I know of. Let me know which one you'd prefer and if there is a simpler way to achieve this.

a)

Implementing it with triggers on the child table (class_student_asc).

Querying the same table in a before insert, update trigger to get the count. Since this gives the mutating table error, this is split into two different statement-level triggers (before-statement and after-statement) to achieve the result..

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

b)

Include a count variable in the class table and lock the parent record for update before inserting a record ito the child table.

So, something like..

create table class(
     class_id number primary key,
     class_name varchar2(50),
     class_attributes varchar2(50),
     class_count INTEGER,
     constraint chk_count_Students check (class_count <=5)
);

and instead of exposing the table class_student_asc for inserts and so on... write a procedure and then use it in all applications..

procedure assign_new_student( 
        i_student_id number,
        i_class_id number)
   is 
   begin
       select class_count
         from class
         where class_id = i_class_id
           for update ; -- or for update nowait, if you want the other concurrent transaction to fail..

   insert into class_student_asc(
       class_id, student_id) 
     values (i_class_id,i_student_id);

   update class
      set class_count = class_count + 1
      where class_id = i_class_id;

   commit;
end assign_new_student;

c)

There are, of course, cases like a user having two email adresses. In such a scenario, the email address itself does not have any attribute and the table could be as simple as

create table user_table
          (
            user_id number,
            user_name varchar2(50),
            user_email_primary varchar2(50),
            user_email_secondary varchar2(50)
          );

However, we cannot extend the same approach for the question above.....as the number of columns and the constraint checks would slow down the inserts and updates . Also, this would mean we'd need a new column added everytime we change the rule.. too.

Please advice.

Answer 1

For Oracle consider this approach.

Create a materialized view summarising the number of students per class. Have the mview refresh on commit and add a constraint to the mview that prohibits a count of more than 50 students per class.

This code demonstrates how to use a fast refresh on commit mview to enforce the student count limit,

insert into class(class_id, class_name) values (1, 'Constraints 101');
insert into class(class_id, class_name) values (2, 'Constraints 201');
insert into student(student_id, student_name) values(1, 'Alice');
insert into student(student_id, student_name) values(2, 'Bob');
insert into student(student_id, student_name) values(3, 'Carlos');

create materialized view log on class_student_asc with primary key, rowid, sequence including new values;

create materialized view class_limit refresh fast on commit as
  select class_id, count(*) count from class_student_asc group by class_id;

alter table class_limit add constraint class_limit_max check(count <= 2);

insert into class_student_asc(class_id, student_id) values(1, 1);
insert into class_student_asc(class_id, student_id) values(1, 2);
insert into class_student_asc(class_id, student_id) values(1, 3);

The constraint will be violated when the transaction is committed, not when the third student is added to the class. This might make a difference to your application code. SQL Developer fails to display the error but sql*plus does display it.