Mike Ro has responded and what he said is absolutely the case for actually coding this solution. I'll try to explain it a bit more.
Here's the tables again: Patient: ID | First | Last | Address | Phone 1 | Joe | Blow | 123 Main | (555)555-5555 2 | Susie | Ort | 444 Main | (555)555-1234 Medication: ID | Name | Description | URL 1 | Aspirin | Headache Relief | http://... 2 | Codeine | Serious Pain relief | http://... 3 | Advil | Pain Relief | http://... 4 | THC | Glaucoma Relief | http://... patient_med: patient_ID | med_ID 1 | 1 1 | 3 2 | 2 2 | 3 2 | 4 The patient_med table is what links patients and the meds they are taking. Let's take it row by row: The first row of patient_med is patient_ID | med_ID 1 | 1 If we look in the patient table for the patient who has an ID of 1, we see that the patient is Joe Blow who lives at 123 Main. So this row is about Joe. Then if we look at the med_ID field of this row, we see 1. So we use that to look up the medicine in the medicine table. We want the medicine that has an ID of 1. That medicine is aspirin. So now because of this linking of patient and medicine in the patient_med table, we see that Joe Blow is taking aspirin. Let's look at the next row: patient_ID | med_ID 1 | 3 Again we see patient_ID is 1. Looking in the patient table, we see that we're talking about Joe Blow again. The med_ID field is different though. If we see what medicine has an ID of 3, we see that it is Advil. So now we know the Joe Blow takes Advil. Between these two rows we know that Joe Blow takes Aspirin and Advil. Let's look at the third row: patient_ID | med_ID 2 | 2 If we look for the patient with ID of 2, we see that it is Susie Ort, so this row will let us know something about Susie Ort's meds. The med_ID of 2 matches the medicine in the medicine table with an ID of 2, which is Codeine, letting us know that Susie has a serious headache. We can continue through the table, matching patient_ID with an ID in the patient table and matching a med_ID with an ID in the medicine table. Each row in the patient_med table works this way, specifying a connection between the patient and the medicine, in this case that the particular patient is using a particular medication. Now your application most likely would *not* need to do what we just did. That's because there's probably not a case where you want to know what meds all of your patients are taking all at the same time. More likely you want to know what medications a particular patient is using, or in the opposite direction, find a list of all the patients who are using a particular medication (maybe to let them know about a class action lawsuit against the manufacturer, or to let them know of a new medicine that has come out that they may be interested in. In either case, the information you want can be fetched with a single SQL query. Let's look at a couple of the scenarios I talked about: You have a patient in mind and want to know what meds they are on: If you have the patient info, you probably have the patient ID from the table. If not you could change this query slightly: SELECT name FROM medicine, patient_med WHERE patient_med.patient_ID='<the patient's id>' AND patient_med.medicine_ID=medicine.ID This will give you a result set that contains one row for each medicine the patient is on. This row will consist of the name of the medication, so you could easily go through each of the rows and list the medications the patient is using. With only a slight change to get the URL or other information, you could also output a link to the webpage about that medication or the description of the med, or both. You have a medicine in mind and want to know who is taking it: Similar to above, if you are looking at information on a med, I am assuming you already have the ID for the medicine. If not you can modify the query to get it at the same time. SELECT first, last FROM patient, patient_med WHERE patient_med.med_ID='<the medicine's id>' AND patient_med.patient_ID=patient.ID This will give you a resultset with the first and last name of each patient who is taking a particular medication. If you know what patient you are interested in and just want to know if they take a particular med, you can query just the patient_med table to see if there is a row that has the patient's ID and the medication's ID. If there is, then you know the patient is taking that med. If you don't get any rows then as far as you know the patient is not taking that medication. I hope this helps to explain it better. Dave On 11/3/05, Mike Ro <[EMAIL PROTECTED]> wrote: > > David, > > you are responsible for adding the data to ALL 3 files. > > You will need 3 routines at minimum - > > one to add a patient (to the patient file) > one to add a medicine (to the medicine file) > one to add a particular patient using a particular medicine (to the link > file) > > so when you get a new medicine, you > 1) add it to the medicine file > > when you get a new patient, you have to do 2 things > 1) add the patient to the patient file > 2) add an entry to the link file for each medicine that the new patient is > using > > Of course 3 routines is minimum - you will probably need routines to edit > and/or delete, as well as search, etc. > > > > > > > > ----- Original Message ----- > From: David Halliday > To: [email protected] > Sent: Thursday, November 03, 2005 2:04 PM > Subject: Re: [php_mysql] Tables : design of "many-to-many" relationship > and speed > > > > Hello David and thank you very much for your response > too but there is something * SO OBVIOUS* to you and I > just cant see it. > > How on earth could table 3 .. the patient_med table .. > do the matching??Honestly now.. there is something Im > missing.. sorry to trouble you but look you are saying > that the first table contains nothing about > medications AND the second table contains nothing > about patients and then the 3rd table, by some magic > or crystall ball reading ?? will tell the user that > Joe is taking Aspirin and Advil while > Susie is on Codeine. Honestly I am missing something > here .. I mean where from is it getting these results > if the two first tables are completely separate > entities. It is not my day today :) > --------------------------------- > David, > > The patients table will contain information that > pertains to that patient > only. So you could have things like name, address, > phone number, etc. > > Then you have a table called medications which would > contain things like the > medication name, a description, a link to a url > containing info about the > med, etc. > > Then you have patient_meds. This table has only two > fields, and the > combination of the two is your primary key. One of the > fields will contain > the primary key (probably an integer or auto_number > field) for the patients > table, and the other field contains the primary key id > to the medication > that patient is taking. > > For example, the patient table could look like > > ID | First | Last | Address | Phone > 1 | Joe | Blow | 123 Main | (555)555-5555 > 2 | Susie | Ort | 444 Main | (555)555-1234 > > The medication table would look like: > ID | Name | Description | URL > 1 | Aspirin | Headache Relief | http://... > 2 | Codeine | Serious Pain relief | http://... > 3 | Advil | Pain Relief | http://... > 4 | THC | Glaucoma Relief | http://... > > etc. > > Then your patient_med table would look like: > > patient_ID | med_ID > 1 | 1 > 1 | 3 > 2 | 2 > 2 | 3 > 2 | 4 > > >From this table you could find that Joe is taking > Aspirin and Advil while > Susie is on Codeine, Advil, and THC. Using this > method, you can have every > patient taking or not taking any combination of any of > the medications in > your database and no space will be wasted holding > empty spots for > medications they are not taking. The other upside to > this is that if you > ever need to add another medication to the table, you > don't have to change > the structure of the table, you can just add the > medication to the > medication table, and then if you have patients using > that medication, just > add appropriate rows to the patient_med table. This > way it is simpler and > more expandable. You can easily add as many patients > and medications as you > could want (assuming your key fields are large enough) > and you don't have to > change your application or your database. > > I hope this helps. > > Dave > > > On 11/3/05, David Halliday <[EMAIL PROTECTED]> > wrote: > > Hi Weng, > > > > Thank you for responding.It is slightly unclear to > me. > > In the first table "patients", do you not fill in > any > > medications details? then there would be a need to > > insert 70 columns with names like MedID1, MedID2, > > MedID3 to MedID70. and/or add details of patients to > > the second table "medication". (unless I missed the > > point altogether!). Regards, David > > > > > > --------------------------------- > > Hi David, > > > > Off the top of my head, you could do 3 tables: > > 1. patients (PatID, Name, other details) > > 2. medication (MedID, Medicine, dosage, etc) you > could > > have 2 aspirins with > > diff. dosages > > 3. patient_med (PMID, PatID, MedID) > > > > To get medicines for a patient: > > Select MedID, Medicine, ... from patients > a,medication > > b,patient_med c where > > a.PatID = c.PatID and b. MedID = c.MedID and > > a.QueryField = FilterValue > > > > To get all patients on a medicine: > > Select PatID, Name, ... from patients a,medication > > b,patient_med c where > > a.PatID = c.PatID and b. MedID = c.MedID and > > b.QueryField = FilterValue > > > > I think this accomodates a lot of variables already. > > The other more senior > > members can help scrutinize my idea. > > > > Hope this helps. > > -Weng > > > > ________________________________ > > From: David Halliday > > > > I wonder if you could advise on this point. > > > > There are already 70 tables, (one for each > medicine). > > Each table has a > > column listing all the patients on that medicine. > For > > example: > > > > ASPIRIN lists 1989 patients's names (in one > > column) > > VALIUM lists 734 patients' names > > so on > > > > There is also one table called PATIENTS. It lists > all > > the 2000 individuals' > > names. Perhaps there is a faster and more efficient > > way! > > > > > > > > > ___________________________________________________________ > How much free photo storage do you get? Store your holiday > snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com > > > The php_mysql group is dedicated to learn more about the PHP/MySQL web > database possibilities through group learning. > > > > SPONSORED LINKS American general life and accident insurance company > American general life American general mortgage > American general life insurance Computer internet security American > general loan > > > > ------------------------------------------------------------------------------ > YAHOO! GROUPS LINKS > > a.. Visit your group "php_mysql" on the web. > > b.. To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. > > > > ------------------------------------------------------------------------------ > > > > [Non-text portions of this message have been removed] > > > > > The php_mysql group is dedicated to learn more about the PHP/MySQL web > database possibilities through group learning. > Yahoo! Groups Links > > > > > > > [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/CefplB/TM --------------------------------------------------------------------~-> The php_mysql group is dedicated to learn more about the PHP/MySQL web database possibilities through group learning. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php_mysql/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
