Hello everybody, I have a question about a schema I'm working on
right now, and I'd like to get the benefit of some other people's
views, the more scathing the better, so really let yourself rip! ;-)

Here's what happens in the real world: an applicant fills out an
application form for a PhD or Masters course - and NO, this is not an
assignment! Every application must have as a minimum a Dean, a Head of
Department (or HOD), and a Main supervisor in an assessment role (much
later on, after an offer is made, a candidate has a main supervisor,
associate, joint-main etc., HOD, and dean associated with the
application. They are likely to be the same as the ones that signed
it off, but this is not guaranteed. I stress: this is all hapening at
the assessment stage.) These person_ids are assigned to the
application as soon as it comes in, but they can change after the
faculty have looked at the application until it is offered or
rejected. A dean can also be a HOD in a small faculty. In rare cases
the dean, HOD and main could all be the same person!

So I have many tables in my schema, including the following 3:
-- fixed-width font ON --

Application, A
--------------
Application_id (PK)
..
..
..

Application_Assignee, AA
------------------------
Application_id (PK1, FK in Application)
Assessor_id (PK2, FK in Person)
Assess_Role_Type (PK3, FK in Assess_Role)
..
..
..

Assess_Role, AR
----------------
Assess_Role_Type (PK)
..
..
..

Rules
-----
1. Applications may have many ADMIN... assessors assigned.
2. Assessors are one of {MAIN, HOD, DEAN, ADMIN_FACULTY,
ADMIN_DEPARTMENT, ADMIN_ALL}
3. The same person may fulfill all the roles.
4. A different person may fulfill each role.
5. There must always be one, and only one, Main, Dean and HOD but
there may be zero or more assessors of other types.

How can I enforce rule 5 in the database? My thoughts so far:
Option 1 (rejected): store {'MAIN', Main_assessor_id} and {'DEAN',
Dean_assessor_id} and {'HOD', HOD_assessor_id} in A and declare them
as FKs in AA.
Reason I rejected it: although there is an optional 1:1 relationship
between A and AA for these relvars so they could be considered as
attributes of A, the solution hard-codes table-based types, duplicates
information and I don't think these PKs really belong in A. It's a
bodge to get around the problem.

Option 2 (rejected): a DEFERRED constraint on insert, update on
Application such that...
select count(*) from AA where AA.application_id = A.application_id
and AA.assess_role_type = 'MAIN' = 1 and
select count(*) from AA where AA.application_id = A.application_id
and AA.assess_role_type = 'DEAN' = 1 and
select count(*) from AA where AA.application_id = A.application_id
and AA.assess_role_type = 'HOD' = 1
Reason I rejected it: a deferred constraint breaks C.J.Date's Golden
Rule.

Option 3 (incomplete solution): check constraint on AA: if there is a
record in AA, it will not be of a type other than {MAIN, DEAN, HOD}
unless one of each of these types exist.
Reason for rejection: doesn't fully work: doesn't require 3 records in
AA among other objections.

Option 4: create a view with 3 inner joins:
select *
from A
inner join aa using (aa.application_id=a.application_id and
aa.assess_role_type='MAIN')
inner join aa using (aa.application_id=a.application_id and
aa.assess_role_type='DEAN')
inner join aa using (aa.application_id=a.application_id and
aa.assess_role_type='HOD')
Essentially this is a new predicate for applications that have been
correctly set up for further processing. This may be combined with
solution 2 to ensure they are unique.
-- fixed-width font OFF --

Are there any other (real) options I am missing out? I feel as if I'm
missing something, but can't put my finger on it. Your ideas, as
ever, are all greatly appreciated.

M

I don't know if this'll work, I'm trying to correct the title!

On Wed, 08 Dec 2010 07:23:33 +0000, Administrator wrote:

OK I think I have the answer. How does this sound:
One of the attributes of Application (I only listed the PK) is
processing_step.
The business rule I was trying to express is "5. An application cannot
proceed from processing_step 2 to processing_step 3 unless it has one,
and only one Main, Dean and HOD role assigned."
In other words, I now believe I need a trigger on table Application that
will compare the before and after values of processing_step, intercept
the transition from step 2 to step 3 and perform the necessary checks and
cause an error if the condition guarding the transition is not satisfied.

I couldn't get to the answer until I asked the question and had a think
about it. Ain't logic great.

On Dec 7, 11:46*pm, VTR250 <goo... (AT) m-streeter (DOT) demon.co.uk> wrote:
....
The above seems to be the most relevant part of your requirements with
respect to your question. Explore the implications of this requirement
and I think a reasonable design will become obvious.

HTH,
TroyK