I've a bad relationship

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

speedo said:
Hi, Can anyone please help me with this project i'm doing for a Dental
Surgery. I've Normalized the data into 4 tables as shown below.

Patients ID (primary key)
name
phone
Click to expand...

Looks ok.

Surgeons ID (primary key)
name
Phone
Click to expand...

Looks ok.

Treatment ID (primary key)
name
price
Click to expand...

Ok. I guess that is the "default" price for each treatment. Presumeably
there will be "actual" prices somewere else, to account for that fact that
patient Tom might get a discount from surgeon Jane, etc.

Appointments ID
date/time (P.Key)
Surgeons ID (P.Key)
Patients ID
Treatment ID
Click to expand...

This depends on what you mean by an "appointment". Say 3 patients visit a
surgeon for a group discussion. Is that one appointment, or three
appointments that happen to be with the same surgeon at the same time? So,
define the term "appointment".

The problem is I get the same Patient with say, 3 appointments at 10.00.
Click to expand...

How can one patient have 3 appointments at the same time? Again, you need to
clearly define what an "ppointment" is, before we can make any suggestions
about the appointment table.

HTH,
TC
 
Hi, Can anyone please help me with this project i'm doing for a Denta
Surgery. I've Normalized the data into 4 tables as shown below.

Patients ID (primary key)
name
phone

Surgeons ID (primary key)
name
Phone

Treatment ID (primary key)
name
price

Appointments ID
date/time (P.Key)
Surgeons ID (P.Key)
Patients ID
Treatment ID

The problem is I get the same Patient with say, 3 appointments a
10.00.
Please help, this is doing my head in, i've been at this for 3 week
soild.
Thank you, thank you, thank you, thank you, in advance.
Speedo.
:
 
In addition to TC's comments, you would be much better off not using the
name "name" for an object that has a name property. Find a naming
convention. Also, it is almost always best to store both a last name and
first name in separate fields. I would recommend:
tblPatients
PatientID (no spaces)
PtFirstName
PtLastName
PtHomePhone
 
Just a little tweak, but, doesn't a dentist (dental surgeon) ever need
dental surgery? If you have persons with names and phone numbers, use a
person table, with a personID. Then, in your appointment table (whatever
defines an appointment), the "SurgeonID" is a personID, and so is the
PatientID.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff Boyce said:
Just a little tweak, but, doesn't a dentist (dental surgeon) ever need
dental surgery? If you have persons with names and phone numbers, use a
person table, with a personID. Then, in your appointment table (whatever
defines an appointment), the "SurgeonID" is a personID, and so is the
PatientID.

Good luck

Jeff Boyce
<Access MVP>
Click to expand...

Hey Speedo,

First, Jeff and Duane have great points; avoid using keywords like
"name" as the name of a field in a DB. Also, people are people
whether they're doctors or patients, and a patient can be a doctor and
a doctor a patient...right? Make a person table for all people using
the common denominators of all people, such as first name, middle
name, last name, SSN, etc...Characteristics that uniquely define
certain types of people can be filtered down into lower level tables,
such as medical license number in tblDoctors. I am unscrewing six
years of data from a database that did not create all people
equally...what a mess!

Chances are you are receiving duplicate query results because there
are duplicates in the tables, because some key has been inputted
incorrectly into a table, or because the relationships/joins are set
incorrectly for the query...take a look at these. If none of these
are the case, try using a DISTINCT query, which will eliminate
duplicates in the query results where all fields in two or more rows
are equal. In non-SQL, you can make a query DISTINCT by going to the
query properties in design view and changing "Unique Values" = "Yes"
under the general tab.

Hope this helps man,

Bryan
 
Back
Top