relationships for database

[phineas629]

Hi all,

I hope I put this question in the correct Forum section.

Here are the rules in my database:

• My database has people for which you can be a client or volunteer.
• Each client can have multiple insurances for which there are multiple authorizations.
• An authorization allows an invoice to be made.
• An invoice allows a client to be visited by a volunteer.

I've attached a relationship for my DB for viewing. Could this relationship work? Should I consider changing my tables? This is my fifth try (I've lost count) at making this DB. I already made the mistake of designing time-consuming forms that I had to trash.
Any help would be great!

[June7]

Can each Insurance have more than one client? If no, don't need Client table, just put PersonID in Insurance table.

Can each Invoice have more than one volunteer? If no, don't need Volunteer table, just put PersonID in Invoice table.

If you say yes to either, I will look at again.

What us UMP?

[phineas629]

Hi June,

There's multiple insurances and each insurance also has multiple clients. Each invoice will have one volunteer. I am hoping to expand additional tables such as volunteer's training efforts and so on so it seems to me I would need a unique id for volunteers?
UMPI is a unique id for each client, I originally used it as a primary key but have decided to use AUTONUMBER for all primary keys now.

Thanks for the help

[June7]

Please define 'insurance' and how can have more than one client. I know I am the only 'client' on insurance policies I buy, what is the 'insurance' entity in your structure?

Are you still populating the UMPI field? If so, set it as Index Yes(no duplicates).

The PersonID can serve in whatever roll the person assumes. Can a client become a volunteer and vice versa?

Still don't think need the intermediate table between Persons and Invoices. If training efforts must be tied to an invoice, table would be:
VolunteerEfforts
InvoiceID (foreign key)
fields for training info

[phineas629]

• A client has an insurance company in charge of him/her. Sometimes the client changes between insurance companies. I need to let the insurance company know that the client has a volunteer spending time with the client. Should I just be having the Insurance company in the invoice and use a query?

• Each client is only allowed a set total number of hours within an AUTHORIZATION (THE INSURANCE COMPANY decides the total hours for the client to use). Should I combine AUTHORIZATION with the insurance table?

i.e. volunteer 1 visits client A for 300 hours for the year.
volunteer 2 visits client B for 280 hours for six months.

• Total number of HOURS for each client must be met but cannot be exceeded within an authorization period.
• Total number of visits for each client cannot be exceeded.
• The volunteer hours are sent to the insurance companies to verify.

thanks!

[June7]

I am really confused and not understanding your business structure. You are an insurance broker? If a client changes insurers isn't that a completely new policy? You have volunteers!? Why is there a limitation on volunteer time, isn't it free? If not, then they aren't volunteers, call them counselors. One insurance can have multiple volunteer (counselor) authorizations? Total authorizations for all insurances client has had cannot exceed time and visits limits?

[phineas629]

I hope this helps. The business I'm in provides services to elderly clients from the lower social economic class. The insurance company sends us a document called an authorization which allows a certain number of hours our counselor can spend with each client. Counselors visit and care for the elderly and record the time spent with them to give to us. We then forward this information as an invoice to the insurance companies.

As always thanks