Many-to-Many Relationships

[FinnMan]

Greetings. I need a bit of advice from those who can help.

I'm working on a database with some many-to-many relationships and the head is getting a bit clouded!! I think I have the structure down but my problem may be queries...

In a nutshell, I'm working with three tables: Doctors, Hospitals, and Addresses. Using a php front-end to populate the database, I'm entering info as I come across it. I may one day enter a doctor and his address and the next day I may enter a hospital and its address. What I'd like to be able to do is query the database for relationships. I.e, query a doctor and find out if his address information is the same as a hospital address OR vice-versa. I may even need to query an address and see if there are any affiliated doctors/hospitals at that location.

Do I have my basic table structure correctly broken up and/or is this merely a query issue?

Thx!
FM

 

[sleipnir214]

You seem to be describing a one-to-many relationship, not a many-to-many.

Classically, you use an additional table to maintain many-to-many relationships.

An example:

Table "people" stores firstnames and lastnames of people:
[tt]+-----------+------------------+
| Field | Type |
+-----------+------------------+
| id | int(10) unsigned |
| firstname | varchar(25) |
| lastname | varchar(25) |
+-----------+------------------+[/tt]

Table "addresses" stores address information:
[tt]+----------+------------------+
| Field | Type |
+----------+------------------+
| id | int(10) unsigned |
| street_1 | varchar(50) |
| street_2 | varchar(50) |
| city | varchar(25) |
| state | char(2) |
| zip_code | varchar(30) |
+----------+------------------+
[/tt]

Table "peoples_addresses" stores the many-to-many relationship between people and addresses:
[tt]+------------+------------------+
| Field | Type |
+------------+------------------+
| people_id | int(10) unsigned |
| address_id | int(10) unsigned |
+------------+------------------+[/tt]

"peoples_addresses" stores only the record ids of the record in people and the record in addresses. A particular address record can be related to multiple people records or vice versa. ______________________________________________________________________
TANSTAAFL!

 

[FinnMan]

OK...lemme recap this to make sure I understand.

I think I understand what your stating with the 'additional' table. This is foreign keys, correct?

However, I'm not sure how my illustration is a 1-to-many. My 3 tables are: Doctors, Hospitals, Addresses. Each hospital can have several doctors, each doctor can be associated with multiple hospitals. Both doctors and hospitals can have multiple addresses. Many to many right?

Thank you for your table illustrations.

If anyone knows of any good "many to many" msyql tutorials on the net I'd be appreciative. I've found some good links but most don't seem to go quite that deep...

Best Regards,
FM

 

[sleipnir214]

Forget the concept of "foreign keys". It's not going to help you conceptually right now.

Imagine you have a written list of doctor's names down the left-hand side of a piece of paper. Then imagine you have a list of hospitals down the right.

Then draw lines which connect a doctor to a hospital. Each line means that doctor works at that hospital. A doctor may work with more than one hospital, in which case more than one line will terminate at that doctor's name.

Now you need a way of recording that relationship. One way would be to number the list of doctors, number the list of hospitals, then on a separate sheet of paper just create a list of pairs of numbers. Each pair represents the endpoints of the connecting lines you drew before.

The additional database table is where you record the information from your two-colum list.

In your case you have many many-to-many relationships.

One many-to-many is the relationship of doctors to hospitals. You need a table to record the relationships of doctors to hospitals. That's one table.

You have a many-to-many relationship between hospitals and addresses. That's yet one more table.

You have a many-to-many relationship between doctors and addresses. That's yet a third.
______________________________________________________________________
TANSTAAFL!

 

[FinnMan]

Ok. Either I'm starting to understand it or I'm greatly confused

I think understand what your saying about the additional tables. So in reality, I can expect 6 tables. A sepearate table listing doctors, hospitals, addresses and additional tables for their existing relationships. Let me type out the tables so you can pat me on the back or slap me, whichever is appropriate:

DOCTORS
+-----------+------------------+
| Field | Type |
+-----------+------------------+
| drid | int(10) unsigned |
| firstname | varchar(25) |
| lastname | varchar(25) |
+-----------+------------------+


HOSPITALS
+-----------+------------------+
| Field | Type |
+-----------+------------------+
| hospid | int(10) unsigned |
| hospname | varchar(25) |
+-----------+------------------+

ADDRESSES
+----------+------------------+
| Field | Type |
+----------+------------------+
| addrid | int(10) unsigned |
| street_1 | varchar(50) |
| street_2 | varchar(50) |
| city | varchar(25) |
| state | char(2) |
| zip_code | varchar(30) |
+----------+------------------+

DR ADDRESSES
+------------+------------------+
| Field | Type |
+------------+------------------+
| drid | int(10) unsigned |
| addrid | int(10) unsigned |
+------------+------------------+

DR HOSPITALS
+------------+------------------+
| Field | Type |
+------------+------------------+
| drid | int(10) unsigned |
| hospid | int(10) unsigned |
+------------+------------------+

HOSP ADDRESSES
+------------+------------------+
| Field | Type |
+------------+------------------+
| hospid | int(10) unsigned |
| addrid | int(10) unsigned |
+------------+------------------+


Does that cover it? And I would use JOIN to do a fully cross-referenced query correct? Thx so much for the useful info....

Regards,
FM