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