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

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 SQL Developer fails to display the error but sql*plus does display it.

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.

Very neat...although, we'd be refreshing the entire MV (for all classes and students) for a every new student... – Rajesh Chamarthi Jul 28 '10 at 14:58 IIRC, with the new values clause on the materialized view log the refresh would be incremental. I have not checked this. – Janek Bogucki Jul 28 '10 at 15:17 @rkumar: Keep in mind that not all databases support materialized views - MySQL and PostgreSQL don't – OMG Ponies Jul 28 '10 at 15:32.

I can think of a couple of ways: 1. Triggers Have an INSERT Trigger on the table that checks on INSERT and does the validation for you. 2.

One-to-One relationships Let's say you want one parent to have only two children. Create two one-to-one relationships.

For every new insert,you'd have to scan through all the tables to see if any of them do not have a child. Also, If you have to get all the children for a given parent, the uqery will have to join all the child tables? – Rajesh Chamarthi Jul 28 '10 at 14:46 @rkumar: (1) would give you a mutating table exception if the trigger is a row trigger.

If, however, you make it a table trigger you can do this. The question is how well (or badly) such a trigger would perform. – Bob Jarvis Jul 29 '10 at 11:21.

Another question had a similar requirement, which you can constrain using a combination of a CHECK constraint with a UNIQUE constraint on a "count" column: stackoverflow.com/questions/3345132/how-....

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.