Optional 1:3+ relationship puzzle

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

> 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
Received on Wed Dec 08 2010 - 08:23:33 CET