Best structure for clients and enquiries (1 Viewer)

[tommac]

Can anyone advise on the best structure for the following situation please?:
I have been asked to look at an access datatbase for a local charity which receives enquiries for services such as doing laundry, performing household tasks etc for elderly/infirm people. The database has been built up by someone else.

They already have a table of clients (with name address, dob information etc), a table of services-provided for clients (dates, type of service etc.) and a table of enquiries-for-services (name/address of enquirer, date of enquiry etc.). Some of the enquiries are by existing clients and some are from new people and may not result in a service, because it is not available from the charity or the enquirer is not eligible.

The charity’s funders require demographic information about the clients and the enquirers and this is difficult to achieve with the clients and enquirers intermingled in two tables.

I think that this would be best achieved by having a People table with name/address etc info of both established clients and ALL enquirers. This would have a PersonID, which would link to enquiries in the enquiries table and to services-provided in the service-provided table. This approach will probably lead to a people table which is cluttered up with a mixture of established clients, one-time enquirers and anonymous enquirers, whereas currently the Clients table only has ‘pure’ clients in it.

Is there a best practice out there for such a situation?

I am quite experienced in access, vba and relational integrity.

Thank you for reading this far.

 

[GinaWhipp]

So far, your idea of having a People table is sound. You can then identify who is a Client and who is not with a checkbox. This will also make it easy to change an Enquirer to a Client.

As for additonal tables... if you post what you are thinking we can adjust or give a thumbs up...

 

[tommac]

Here is what I'm thinking so far. The original db has been built by someone else, but it has reached the limits of their design. I'd like to be sure that my proposals are likely to work, before I dive in and make radical changes. zipped.doc file attached ( I hope).

regards and thanks in anticipation,
tommac

 

[GinaWhipp]

Okay, nope... service like Mini Bus Hire, Handyman Jobs, etc... should be in one table. People and Organizations should be in one table. Have a look at...

http://www.access-diva.com/dm5.html

You also have to be careful with those field names, have a look at...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page...
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials
Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/
Naming Conventions…
http://www.access-diva.com/d1.html
Other helpful tips…
http://www.access-diva.com/d11.html

 

[tommac]

Thanks for the comments and all of the very useful links. One query though, I was going to keep the different types of jobs in separate tables(tblHandymanJObs, tblMiniBusHire etc.) linked one-to-one, with the ServiceRecordID in the record_of_service_table because each type of job has very different fields holding the details. I've seen this technique used in other databases and I understand that, because of the one-to-one links, the individual job tables are effectively combined into one virtual table. The advantage being that you don't end up with one huge table with many fields, many of which have null entries.

Thanks again

 

[GinaWhipp]

There is only one way to say this... Those other databases you saw that in are WRONG. You will have a mightmare with forms, queries and reports when you are later trying to retreive this information. Down the road you'll be pulling your hair out. Services go in one table, name it tblServices... put fields in there to accomodate all kinds of services.

Big tables are not a problem as long as it's normalized. And empty fields take up no space until data is actually entered into the field.

 

[tommac]

Thanks,
One table it is then.
Regards,
Tommac

 

[GinaWhipp]

Okay... my last post might have been a bit over the top but I see zi got the point across. You're welcome! We're here if you have any more questions...