A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Designing a patient management



 
 
Thread Tools Display Modes
  #1  
Old March 10th, 2010, 05:10 PM posted to microsoft.public.access.tablesdbdesign
Simba
external usenet poster
 
Posts: 7
Default Designing a patient management

Being a newbie to access, I have been given a task of creating a db for the
patient management and tracking. This DB will have around 50 patients per
year. It will contain the history of patient's contact info, pediatrician,
case manager,Genetic center info, Insurance info and so on. I have created
the tables. pls review them and suggest me your ideas to improve the DB, as
mine maynot be best.

tblPatients
RegID - PK
PatientID,FirstName,LastName,DOB,Gender,Hospital,M A,CaseStatus,Diagnosis,Address,City,Zip,county,MFi rstName(mom),MLastName,MAddress,MCity,MState,MZip, MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father ),FLastName,FAddress,FCity,FState,FZip,FHomePhone, FcellPhone,CurrentPed,FormerPed,GenCenter,Genetici st,CaseManager,CaseManagement

tblPediatrician_1 and also tblPediatrician_2
PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax

tblGenCenter
center_Id-PK,Name,Address,City,state,zip,phone,Fax

tblGencounselor
CounselorID-PK,FName,LName,Phone,Pager,Fax

tblCaseMgr
MgrID-Pk,FName,LName,Phone,Pager,Fax

tblCaseMgmt
MgmtId-Pk,Name, address,........

tblInsurance
InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone ,fax,insuranceName,policyno,CS_Phone

tblInsuranceType
InsuranceType-ID-Pk,RegID,InsuranceID

tblGuardian
guardianId-pk,Refdate,type,fName,LName,address,state,city,zip ,hPhone,Cphone,wphone

tblpatientguardian
patguardianID-Pk
Regid
GuardianID
  #2  
Old March 10th, 2010, 05:19 PM posted to microsoft.public.access.tablesdbdesign
Daniel Pineault
external usenet poster
 
Posts: 658
Default Designing a patient management

Not an answer to your question, but if you are in the US, keep in mind the
HIPAA privacy and security requirements, Pat. an Access
database cannot be completely secured, so you have to consider whether it
will satisfy HIPAA regulations.

See:
http://www.microsoft.com/office/comm...8-bec2bf26d20c
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Simba" wrote:

Being a newbie to access, I have been given a task of creating a db for the
patient management and tracking. This DB will have around 50 patients per
year. It will contain the history of patient's contact info, pediatrician,
case manager,Genetic center info, Insurance info and so on. I have created
the tables. pls review them and suggest me your ideas to improve the DB, as
mine maynot be best.

tblPatients
RegID - PK
PatientID,FirstName,LastName,DOB,Gender,Hospital,M A,CaseStatus,Diagnosis,Address,City,Zip,county,MFi rstName(mom),MLastName,MAddress,MCity,MState,MZip, MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father ),FLastName,FAddress,FCity,FState,FZip,FHomePhone, FcellPhone,CurrentPed,FormerPed,GenCenter,Genetici st,CaseManager,CaseManagement

tblPediatrician_1 and also tblPediatrician_2
PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax

tblGenCenter
center_Id-PK,Name,Address,City,state,zip,phone,Fax

tblGencounselor
CounselorID-PK,FName,LName,Phone,Pager,Fax

tblCaseMgr
MgrID-Pk,FName,LName,Phone,Pager,Fax

tblCaseMgmt
MgmtId-Pk,Name, address,........

tblInsurance
InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone ,fax,insuranceName,policyno,CS_Phone

tblInsuranceType
InsuranceType-ID-Pk,RegID,InsuranceID

tblGuardian
guardianId-pk,Refdate,type,fName,LName,address,state,city,zip ,hPhone,Cphone,wphone

tblpatientguardian
patguardianID-Pk
Regid
GuardianID

  #3  
Old March 10th, 2010, 05:53 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Designing a patient management

Generally, I can see 4 areas you'll need/want to come up to speed on if you
are going to "create a database for ..." pretty much anything!

1) Relational database design & normalization (Access expects data
organized this way)
2) Access tricks/tips -- Access doesn't do things the same way Word or
Excel do things
3) Graphical user interface design -- if it isn't drop-dead simple to
use, it won't get used
4) Application development -- if you've never built one, how do you know
what to do?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Simba" wrote in message
...
Being a newbie to access, I have been given a task of creating a db for
the
patient management and tracking. This DB will have around 50 patients per
year. It will contain the history of patient's contact info, pediatrician,
case manager,Genetic center info, Insurance info and so on. I have created
the tables. pls review them and suggest me your ideas to improve the DB,
as
mine maynot be best.

tblPatients
RegID - PK
PatientID,FirstName,LastName,DOB,Gender,Hospital,M A,CaseStatus,Diagnosis,Address,City,Zip,county,MFi rstName(mom),MLastName,MAddress,MCity,MState,MZip, MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father ),FLastName,FAddress,FCity,FState,FZip,FHomePhone, FcellPhone,CurrentPed,FormerPed,GenCenter,Genetici st,CaseManager,CaseManagement

tblPediatrician_1 and also tblPediatrician_2
PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax

tblGenCenter
center_Id-PK,Name,Address,City,state,zip,phone,Fax

tblGencounselor
CounselorID-PK,FName,LName,Phone,Pager,Fax

tblCaseMgr
MgrID-Pk,FName,LName,Phone,Pager,Fax

tblCaseMgmt
MgmtId-Pk,Name, address,........

tblInsurance
InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone ,fax,insuranceName,policyno,CS_Phone

tblInsuranceType
InsuranceType-ID-Pk,RegID,InsuranceID

tblGuardian
guardianId-pk,Refdate,type,fName,LName,address,state,city,zip ,hPhone,Cphone,wphone

tblpatientguardian
patguardianID-Pk
Regid
GuardianID



  #4  
Old March 10th, 2010, 06:00 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Designing a patient management

tblPediatrician_1 and also tblPediatrician_2

Big mistake. All your Pediatricians should be in the same table. What if you
have a 3rd Pediatrician?

Also putting in all the mother and father info in the tblPatients could be a
mistake. (1) what about other relatives that might have legal rights such as
stepfathers and grandparents? (2) What if two siblings are also patients? It
might be better to have a tblRelatives. Of course with as few records as you
suggest, you could denormalize it.

Here's something to consider: Has anyone looked at trade journals or other
resources to see if someone already has a product that will meet your needs?
It will almost always be faster and cheaper to buy and off-the-shelf solution
than build your own.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Simba" wrote:

Being a newbie to access, I have been given a task of creating a db for the
patient management and tracking. This DB will have around 50 patients per
year. It will contain the history of patient's contact info, pediatrician,
case manager,Genetic center info, Insurance info and so on. I have created
the tables. pls review them and suggest me your ideas to improve the DB, as
mine maynot be best.

tblPatients
RegID - PK
PatientID,FirstName,LastName,DOB,Gender,Hospital,M A,CaseStatus,Diagnosis,Address,City,Zip,county,MFi rstName(mom),MLastName,MAddress,MCity,MState,MZip, MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father ),FLastName,FAddress,FCity,FState,FZip,FHomePhone, FcellPhone,CurrentPed,FormerPed,GenCenter,Genetici st,CaseManager,CaseManagement

tblPediatrician_1 and also tblPediatrician_2
PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax

tblGenCenter
center_Id-PK,Name,Address,City,state,zip,phone,Fax

tblGencounselor
CounselorID-PK,FName,LName,Phone,Pager,Fax

tblCaseMgr
MgrID-Pk,FName,LName,Phone,Pager,Fax

tblCaseMgmt
MgmtId-Pk,Name, address,........

tblInsurance
InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone ,fax,insuranceName,policyno,CS_Phone

tblInsuranceType
InsuranceType-ID-Pk,RegID,InsuranceID

tblGuardian
guardianId-pk,Refdate,type,fName,LName,address,state,city,zip ,hPhone,Cphone,wphone

tblpatientguardian
patguardianID-Pk
Regid
GuardianID

  #5  
Old March 10th, 2010, 07:26 PM posted to microsoft.public.access.tablesdbdesign
Simba
external usenet poster
 
Posts: 7
Default Designing a patient management

Hi!

Thanks for pointing out. In fact, I have only one tblPediatricians. I was
looking at the relationship diagram while typing the previous message and
made such a mistake. Those two relationships will be pointing to CurrentPed
and FormerPed respectively but still from the main tblPediatrician.

I am trying to normalize as much as possible. I put the mother and father in
the main tblPatient being one to one. I thought, I have a separate
tblGuardian for capturing stepmom and dad or grandparent.

Thanks!


"Jerry Whittle" wrote:

tblPediatrician_1 and also tblPediatrician_2


Big mistake. All your Pediatricians should be in the same table. What if you
have a 3rd Pediatrician?

Also putting in all the mother and father info in the tblPatients could be a
mistake. (1) what about other relatives that might have legal rights such as
stepfathers and grandparents? (2) What if two siblings are also patients? It
might be better to have a tblRelatives. Of course with as few records as you
suggest, you could denormalize it.

Here's something to consider: Has anyone looked at trade journals or other
resources to see if someone already has a product that will meet your needs?
It will almost always be faster and cheaper to buy and off-the-shelf solution
than build your own.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Simba" wrote:

Being a newbie to access, I have been given a task of creating a db for the
patient management and tracking. This DB will have around 50 patients per
year. It will contain the history of patient's contact info, pediatrician,
case manager,Genetic center info, Insurance info and so on. I have created
the tables. pls review them and suggest me your ideas to improve the DB, as
mine maynot be best.

tblPatients
RegID - PK
PatientID,FirstName,LastName,DOB,Gender,Hospital,M A,CaseStatus,Diagnosis,Address,City,Zip,county,MFi rstName(mom),MLastName,MAddress,MCity,MState,MZip, MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father ),FLastName,FAddress,FCity,FState,FZip,FHomePhone, FcellPhone,CurrentPed,FormerPed,GenCenter,Genetici st,CaseManager,CaseManagement

tblPediatrician_1 and also tblPediatrician_2
PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax

tblGenCenter
center_Id-PK,Name,Address,City,state,zip,phone,Fax

tblGencounselor
CounselorID-PK,FName,LName,Phone,Pager,Fax

tblCaseMgr
MgrID-Pk,FName,LName,Phone,Pager,Fax

tblCaseMgmt
MgmtId-Pk,Name, address,........

tblInsurance
InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone ,fax,insuranceName,policyno,CS_Phone

tblInsuranceType
InsuranceType-ID-Pk,RegID,InsuranceID

tblGuardian
guardianId-pk,Refdate,type,fName,LName,address,state,city,zip ,hPhone,Cphone,wphone

tblpatientguardian
patguardianID-Pk
Regid
GuardianID

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:51 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.