eliminating duplicate records in database

[knoxbapweb]

Hi,
I am working with a databse that holds physician info (first name, last name, specialty, facility affialiated with, etc).

Users on the website can search by specialty only, last name only, or search by specialty and facility.

This has not been a problem for me until recently, when our hospital opened an additional facility and many of the docs that are affialiated with the existing facility are, now also, affialiated with the new facility.

The first thing I knew to do was to duplicate those records and change the facility to the new one.

This is not working to good because, if people just choose to search by last name, or specialty only, they will see duplicate records returned on the results page.

I cannot figure out how to solve this.... can somebody please help me?? Thank You!

[skhanal]

You should normalize your database by adding a table for location and doctorlocation

location
---------
locationid
location name

doctorlocation
--------------
doctorid
locationid

This way you maintain only one row in doctor table. But you can always display two locations doctor works in by joining doctor table to doctorlocation and location table.

[knoxbapweb]

".... But you can always display two locations doctor works in by joining doctor table to doctorlocation and location table."

––– what do you mean , joining?

All the addresses and phone numbers, etc are different for each doctor for both locations...??

[skhanal]

The idea of normalization is to store information about an entity at only one place. Your design of duplicating doctor record is very difficult to maintain, because if any information about a doctor changes you have to update multiple records, tables.

You can store phone number in doctor location table.

Joining means using JOIN syntax in SELECT statement.

for example
select doctorid, doctorlocation
from doctor
join doctorlocation
on doctor.doctorid = doctorlocation.doctorid