TroubleSome TALBES and their Relationships

[doctorsorabhjain]

Hello,
This is my first post in here and i wanted some help. Well I am a doctor and i am preparing this access dbms only for my clnic use.i have started learning access from some past few months only, so i tried to figure out what here is going wrong. as according to the rule, there should be no field repititions and try to reduce data duplicacy as much as possible.
Firstly,i will try to tell you what is my thought process regarding this tables. One Person can have many medical emergencies, many phone numbers. but some patients being of the same address,also sometimes,phone no. where they can be same.in address, most of the people can have same station name, village,city,state also, so i thought to put them in different tables. I well tried many things, but please do reply for my query and also ELABORATE(i.e., please explain in detail) it. also i will link to this many other tables, so please do help.

 

[jdraw]

I strongly suggest you watch these videos to fully understand the concept and go through the processes involved. Pay particular attention to Normalization.

Here are free video tutorials that you may find helpful.
http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.

Also, many people do not have acc2007 or 2010 and can not open the accdb file. Post a copy in mdb (2003 format).

 

[doctorsorabhjain]

Hello,
Thanks for your prompt response and i am going through the videos. so uptill now what i was able to understand is what i had tried to adjust in the file. Please feel free to correct me as i am really interested to learn.

Thanking You,

Regards,

Dr. Sorabh Jain

 

[jdraw]

There are a number of free existing data models at
www.databaseanswers.org Some of these may be helpful to you.

I looked at your database and the relationships.

All of the information related specifically to the Patient can go in the Patient Table. These may be directly in the table, or a foreign key pointing to the proper value in a related table.

Name, Address, City, Station, DOB, PrimaryPhone, phonetype,email

It is recommended that you use an autonumber primary key for Patient.
You can get the Name and other attributes through relationships.

As for your Addresses with City, Station, State (from sample data, I see you are in Mumbai). These tables are related in some sort of hierarchy and you can have Tables for this.
Supposing the hierarchy is State contains City and City contains Station. You could have a table of States, a table of Cities and a table StateHasCity which links States and the Cities within that State. Also you could have a table of Stations and CityHasStation that relates City and the Stations within that City. I don't know the exact geographical breakdown, so I'm suggesting a concept not a specific structure. And there may be other structures that may work in your situation.

You could have Titles in a separate Table, and use that table to ensure data entry has consistent spelling and is from an authorized list of possible values.

Your names of tables and fields should not have special character (') or spaces.

Arew you dealing only with emergencies, or are/will there be regular visits (checkups/followups..)?

Can you identify a list of potential emergencies and put them in a table ( you can always add more) but such a table will assist with data entry and can reduce/eliminate spelling errors.

Enough for now.

Good luck with your project.