I am building a database on Access that, among other things, tracks who has referred patients to our office (I work in an optometrist's office which does a specialized kind of therapy).
I don't have much experience creating relational databases, and I am wondering if I'm heading the right direction. Before posting here, I've asked 5 of my computer programmer friends for help, but suddenly every is busy... So here goes.
1 patient can have Many referrals
- my son's teacher and my coworker referred me here (2 referring sources)
- my doctor told me about you, and then I saw your website (2 referring sources)
1 referral can consist of:
- an individual (Dr Blah-Blah, or My Friend Jane Doe)
- an organization (Doctors Group)
- an individual that is part of an organization (Dr. So-and-So from Doctors Group)
- a media source (website, pamphlet, commercial, tv show, etc)
Often it's not possible to determine which individual at an organization referred the patient (because patients can't remember), so only the organization is recorded. But sometimes it is possible, so the individual and the organization are recorded. And sometimes individuals are not part of any organization.
This is the partial design that I came up with: http://postimg.org/image/uipaq7rrl/
- Patients Table
- Referring Media Table
- stores details of media sources
- Referring Organization Table
- stores the names/details of organizations
- Referring Individuals Table
- stores names/details of individuals
- uses Ref Org ID as a foreign key, in case individuals are members of an organization
- Referrals Table
- uses the Patient ID as a foreign key to link the referrals to the patients
How should I link the Referrals Table to the Ref Org, Ref Indv, and Ref Media tables?
I came up with something like this: http://postimg.org/image/qtpoiflmv/
But that seems redundant and leaves a lot of blank spaces, depending on the type of referral: http://postimg.org/image/oepj99ohz/
What should I do? What is a good way to build relationships between these tables?
