Relationship Help

[Annette Marie]

Hi. I'm in the middle of creating a database, which I made the mistake of thinking I was doing good on...until I got further into and realised I could be making major mistakes.

I have attached a copy of an excel file, in which I have listed each of my tables and their fields as a reference. Thought it might be easier seeing it all laid out in front of you rather than looking at the disaster database I have created.

Basically, I have clients (Table 1) which I need to record the info for Tables 2, 3, 4, 7, and 8 on. I thought I was to make the relationship to each by the client ID. Was I better off not seperating some of them into their own tables and just keep it under table 1, i.e. Tables 2, 3, and 4?

In order to record the info for tables 7 and 8 (possibly more categories in the future), I have a form with tabs on it. The Form itself will have the Return ID (that I would like to be shared between the two tables) and the month beginning (also shared between the two), would I have been better to just have them both on the same table? and just have the seperate fields on tabs as they collect data differently?

Maybe if I explain what I'm trying to achieve it might make this easier to understand.

Service providers will be able to go on the database, create a service return sheet for a client for the given month (or add to one already started for that month). I pull the clients name from a drop down list and the address and such should fill in. If I am a respite provider I would click on the tab for respite and enter the info in the line items. If I am home help, I click on my tab, and enter the info in the line items. And so on. I am trying to create one return for each client, for each month, no matter how many categories they received as each category will have their own tab.

I hope I didn't confuse the issue more and that you are able to follow me. I'm trying to avoid creating more tables than needed, but do it right so that all the searches and info is accurate and working properly.

Database_Tables_and_Fields.zip

[Fitch]

Question 1: should the fields in the Referral Info, Respite Info, and Home Help Info tables be moved to the Client Info table?

That depends. Can there be more than one of each of those things for each client? If so, then it probably makes sense to have them in separate tables.

Question 2: should Respite Service Hours and Home Help Service Hours be in the same table?

Almost all the fields are the same, so I'd say very likely yes. Perhaps call the table "Service Hours" and create a "Type" field where you can specify whether it's Respite or Home Help.

Hope I've interpreted your questions correctly. It might help you to think more about what your reporting requirements are, that generally dictates how you structure the data.

[Annette Marie]

Ideally a client will only be referred once and then just reviewed after that for changes. On their referral, or review, is when the respite info and home help info would be decided. It can change upon review. I know there is a way to update a persons info, which updates for future actions...but how do I make sure that it doesn't change previous instances? For example...on referral I may be given five hours of home help, which I got for lets say the first five reporting hours. But then I was changed to three hours so all the ones from then on need to say three. But if you look back at old reports I want it to show what the given hours were at that time. Do you follow me? I hope I didn't totally confuse you.

As for my second question, you're right, most of the info is the same and I thought it could be combined but I wasn't sure.

Thanks for the help so far!

[comment]

I'd suggest you look at the attached sketch. This would allow you to add service types without modifying your structure.

[Fitch]

Whenever you have info that may change in the future, and you want to keep the info on old records, that indicates that the data needs to be stored in a field on the old records.

Example: the tax rate on an invoice.

There would be a master tax rate somewhere, perhaps in the client record if the clients are from different states. When a new invoice is generated, the tax rate field on the invoice record would look up the info from the client record. The tax rate is stored on the invoice. That way, when the client's tax rate changes, it does not affect previous invoices.