Database Designs & Relationships

[jciabattoni]

Good afternoon,

I'm creating a database for the following purpose:

Giving Comfort is a nonprofit of the McKesson Foundation which provides Comfort Kits to low-income cancer patients. We form partnerships with Distribution Network Partners (DNPs) who order the Comfort Kits and provide them to the patients at no charge (to either the DNPs or the patients). DNPs are usually one-off locations such as a medical center. However, there are also partnerships with larger organizations such as American Cancer Society which exists in multiple locations across the US. We would like to track each DNP location, including if they are part of a larger system, their status (current, prospect, inactive), the contacts at each facility, their order history, which McK Location they are closest to and if they are a McKesson Customer.



Additionally, McKesson Foundation and McKesson Corporate locations are looking to get more involved with the DNPs. DNPs can apply for grants through the McKesson Foundation so we would like to track the amount of each grant they receive, the date it was awarded, what it was used for and how many patients were served by the grant money. McKesson Corporate locations can work with DNPs who host events to hand out the Comfort Kits, help at the facility, etc. With this we are looking to track the event information such as which McKesson location participated at which DNP, how many employees volunteered, how many patients were served, and the date of the event.

Below is my current relationship map in Access.


I'd love some feedback on if this is correct or if I should change how things are connected. I know that databases are open to interpretation, but as I start to build forms so my team can input the information easily I'm having a hard time. I wanted to make sure my design is correct before I go back to figuring out why the forms aren't coming to me naturally.

Thank you for your feedback!
~Jaime

[NTC]

One can only advise in general: there may be too many tables 'location'; and some thought might be toward consolidating them into a single table or at least fewer, possibly using a 'role' field to differentiate between them for other purposes. The other tables look about right as far as I can tell without knowing things more in depth.

[aksheykumar]

in cases where the same hazEvent is mentioned in several papers but different responseTYpes are mentioned, the query returns a record for each responseType and links it to each article. But I only want the responseType that is mentioned in the article.
What is the reason for this incorrect association and what can I do to fix it? .. . . .


_____________
sunny