Re: Optional 1:3+ relationship puzzle
From: Administrator <google_at_m-streeter.demon.co.uk>
Date: Wed, 08 Dec 2010 11:23:06 GMT
Message-ID: <uUJLo.4412$MF5.609_at_viwinnwfe02.internal.bigpond.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
Date: Wed, 08 Dec 2010 11:23:06 GMT
Message-ID: <uUJLo.4412$MF5.609_at_viwinnwfe02.internal.bigpond.com>
On Wed, 08 Dec 2010 07:23:33 +0000, Administrator wrote:
> 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
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. Received on Wed Dec 08 2010 - 12:23:06 CET