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] ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/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/
