Darren DICK
d.dick at uws.edu.au
Mon Nov 22 16:57:24 CST 2004
Hi Richard Just had the same design Q myself last week The consensus was to merge My Client and Organisation tables into 1 table and have an Identifier similar to IsClientOrOrg (Client = 1 Org = 2) Saved me a whole bunch of issues and I have moved on from that 'difficult' problem Hope this helps See ya DD -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Griffiths, Richard Sent: Tuesday, 23 November 2004 12:16 AM To: Access Developers discussion and problem solving Subject: RE: [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 -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com