[AccessD] Design Problem

Richard - I think if it was me I would only have one client table, from what you have said, but with 3 separate fields there, eg.

'Job' table:
Job ID (autonumber)
RequestorID (related to Client ID in Client table)
PrincipalClientID (related to Client ID in Client table)
RecipientID (related to Client ID in Client table)

'Client' table
Client ID (autonumber)
Name details
Address details


The fields RequestorID, PrincipalClientID and RecipientID would all relate to one Client table.

That way all 'client' data would be stored in the one table and never duplicated, and you would have the flexibility of having any combination of the 3 fields in the Job table. The only mandatory field (it appears) would be the RequestorID.

HTH

Kath

  ----- Original Message ----- 
  From: Griffiths, Richard 
  To: AccessD at databaseadvisors.com 
  Sent: Monday, November 22, 2004 10:28 PM
  Subject: [AccessD] Design Problem


  Hi Group

   

  I am having a brain blockage - can anyone help. I will try to be as
  brief as possible.

  I can't figure out how to design this one.  Seems like different options
  - not sure which way to go.

  Here goes (may not be as complicated as it appears) - sorry its so long.

   

  Scenario:

   

  Client_1 (which can be an organisation or individual) can request a Job
  (interpretation) 

  The Job can be carried out on behalf of a second Client_2  or not.

  The job may have a Patient/recipient (client_3??) of the job
  (interpreation) or not.

   

  Example 1

  Charity OrgA (Client 1) requests an interpretation for a Medical Centre
  (Dr Smith - Client 2) and 

  Mrs Jones (client 3/Patient) is the person receiving teh interpretation.

   

  Example 2

  Charity OrgA (Client 1) requests an interpretation for a itself (Client
  1) and the Recipient/Patient is itself(client_1) 

   

  Example 3

  Medical Centre (was previously Client 2) requests an interpretation for
  a itself (Client 2) 

   

  What I am confused is whether to have one Client Table (that accomodates
  Orgs/Customers and Individuals/Patients)

  Or have 3 separate tables Client_1 table, Client_2 (aka Customer) table
  and a Patient/Person Table.

   

  All 3 entities hold address/contact info (Client 1 table needs to store
  invoice/finance data as well)

   

  To give a job table like this

   

  JobTable

  ========

  JobID

  ClientID_1--------->Client_1_table

  ClientID_2--------->Client_1_table

  ClientID_3--------->Client_1_table

  Other fields

  .....

   

  or

   

  JobID

  ClientID_1--------->Client_1_table

  ClientID_2--------->Client_2_table

  ClientID_3--------->Client_3_table

  Other fields

  .....

   

  The second option means that when Client_1 requests a job for itself the
  address data etc is duplicated into the Client_2 and Client_3 tables 

  - does this really matter?  Equally the Medical Centre (Client_2) may
  next week be Client_1 (request a job/service itself)

   

  Finally one more question is that I can't decide whether to do this as
  well - even thought there is a one-to-one job_client relationship

  (rather than store Client_1_ID, Client_2_ID, Client_3_ID in job table as
  above) do this????

   

  JobTable

  ========

  JobID

  Other fields

  .....

   

  JobClient_1_Table

  =================

  JobID

  Client_1_ID

  ......

   

  JobClient_2_Table

  =================

  JobID

  Client_2_ID

  ......

   

  JobClient_3_Table

  =================

  JobID

  Client_3_ID

  ......

   

   

  Any thoughts?

  Many thanks

   

  Richard

   

   

  -- 
  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com