[AccessD] Design Problem

Thanks Kath, Chris for your reply - this was my original thinking but
then I had doubts. As the Recipients really may only have a name (and
maybe address) I wondered whether they should have their own table.  I
will simply have to add logic to my FE (and have fields such as
IsOrganisation, IsRecipient flags/fields on my Client table) so that
when adding new Requestor Clients it displays invoice, vat etc details -
but not for Recipient Clients.
Richard


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kath Pelletti
Sent: 22 November 2004 11:41
To: Access Developers discussion and problem solving
Subject: Re: [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
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com