3:n relationship puzzle

From: VTR250 <google_at_m-streeter.demon.co.uk>
Date: Tue, 7 Dec 2010 21:46:10 -0800 (PST)
Message-ID: <b6d20070-b56b-4c39-bf98-3e5132a7a63e_at_z26g2000prf.googlegroups.com>

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 - 06:46:10 CET