[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