K
kmeezy
Guest
Hi, i'm in the process of migrating a database of about 5,000 records from excel into access for a doctor's office where i work at. I've been reading a lot about relational databases and conforming to the 5 normal forms and such but i'd still like to get opinions from you experts on how i should structure my database. OK, here's my scenario:
The current excel spreadsheet contains membership information in these fields:
Membership #, Last Name, First Name, Approval Date, Expiration Date, Doctor, Birthday (of patient), Notes
It's pretty simple other than a couple of things which I'll list below:
(1) I'd like to improve on the spreadsheet when i migrate to access by allowing us to store more than the most recent Approval and Expiration Dates (along with the doctor they saw)--Currently when a patient renews his/her membership we just overwrite the old dates and doctor with the latest information (in the spreadsheet). I'd rather save the old dates and doctor info, so that we could track a patient's membership/renewal history instead of just knowing their most current membership info.
My solution to this would be to create a subdatasheet with the Approval, Expiration, and Doctor field in it; linked to the Patient database by the Patient Membership #. So, I'd create an Approval/Expiration table that would have a many-to-one relationship with the main Patient table. What do you guys think? Is there a better way to store the dates other than a subdatasheet?
(2) Some of our patients are unable to come to our office to purchase medicine (marijuana
due to physical impairments, so they are assigned a "Caregiver" which is allowed to pick up medicine for them. I refer to patients that have Caregivers as "Care-recievers". From the database perspective, a Caregiver is the same as a patient--the same information is gathered as far as Approval and Expiration dates of membership, Doctor seen and such. Currently in the Excel spreadsheet, Caregivers are entered just like patients but we make a note in the Notes field as to which patient they are giving care to. So there is a link or relationship between a Caregiver and a Care-reciever.
To solve this issue i was going to create a field in the Membership table with a combo box which would specify the member types: "Care-Reciever", "Care-Giver", and "Patient", where a Patient is just a regular member without a Caregiver. Most of our members are just "Patients". My main issue is what's the best way to create a relationship between Care-Givers and Care-Recievers? Since the info taken on Patients, Care-Givers and Care-Recievers is exactly the same, I was thinking it would be better to just list them together in the main Membership table, rather than having separate tables for them. But if i did this i'd need to create some field which would store the related 'giver' or 'reciever' Membership # so that we'd know who a patient's caregiver was. What do you think? Is it possible to just have one main Membership table or is it better to have separate tables for Caregivers, Carereceivers and Patients although they are so closely related?
----------------
Note: I also plan on saving the Doctor's info in a related Doctor Info table, but this shouldn't affect the Membership table structure. Also, right now i can't use the the Membership # as the primary key since previous employees were giving CareGivers and CareRecievers the same membership # so that they could figure out the relationship between them (i.e. CareReciever #1234 would be related to CareGiver #CGVR-1234).
Well, I hope I've explained my situation clearly. I'm open to any suggestions that you guys have about structuring our new database.
The current excel spreadsheet contains membership information in these fields:
Membership #, Last Name, First Name, Approval Date, Expiration Date, Doctor, Birthday (of patient), Notes
It's pretty simple other than a couple of things which I'll list below:
(1) I'd like to improve on the spreadsheet when i migrate to access by allowing us to store more than the most recent Approval and Expiration Dates (along with the doctor they saw)--Currently when a patient renews his/her membership we just overwrite the old dates and doctor with the latest information (in the spreadsheet). I'd rather save the old dates and doctor info, so that we could track a patient's membership/renewal history instead of just knowing their most current membership info.
My solution to this would be to create a subdatasheet with the Approval, Expiration, and Doctor field in it; linked to the Patient database by the Patient Membership #. So, I'd create an Approval/Expiration table that would have a many-to-one relationship with the main Patient table. What do you guys think? Is there a better way to store the dates other than a subdatasheet?
(2) Some of our patients are unable to come to our office to purchase medicine (marijuana
due to physical impairments, so they are assigned a "Caregiver" which is allowed to pick up medicine for them. I refer to patients that have Caregivers as "Care-recievers". From the database perspective, a Caregiver is the same as a patient--the same information is gathered as far as Approval and Expiration dates of membership, Doctor seen and such. Currently in the Excel spreadsheet, Caregivers are entered just like patients but we make a note in the Notes field as to which patient they are giving care to. So there is a link or relationship between a Caregiver and a Care-reciever. To solve this issue i was going to create a field in the Membership table with a combo box which would specify the member types: "Care-Reciever", "Care-Giver", and "Patient", where a Patient is just a regular member without a Caregiver. Most of our members are just "Patients". My main issue is what's the best way to create a relationship between Care-Givers and Care-Recievers? Since the info taken on Patients, Care-Givers and Care-Recievers is exactly the same, I was thinking it would be better to just list them together in the main Membership table, rather than having separate tables for them. But if i did this i'd need to create some field which would store the related 'giver' or 'reciever' Membership # so that we'd know who a patient's caregiver was. What do you think? Is it possible to just have one main Membership table or is it better to have separate tables for Caregivers, Carereceivers and Patients although they are so closely related?
----------------
Note: I also plan on saving the Doctor's info in a related Doctor Info table, but this shouldn't affect the Membership table structure. Also, right now i can't use the the Membership # as the primary key since previous employees were giving CareGivers and CareRecievers the same membership # so that they could figure out the relationship between them (i.e. CareReciever #1234 would be related to CareGiver #CGVR-1234).
Well, I hope I've explained my situation clearly. I'm open to any suggestions that you guys have about structuring our new database.
