David, I wanted to echo what Weng said, and highlight the key change you need to make to your schema: you don't want a separate table for each medicine! In your current design, you're storing information in the table names themselves, which is a very inefficient way to do it. You want to replace those 70 medicine tables with one table that has 70 rows, so you have just a row for each medicine instead of a whole table. This is called "normalization" in database jargon.
You're already normalized somewhat, in having the patients' names in their own table. We're just adding to that idea, and reducing your current 71 tables to 3! The trick is to use a "link table," which in Weng's schema is called "patient_med." Link tables are the way you implement a many-to-many relationship in SQL. (Since you used the jargon "many-to- many" in your subject, it sounds like you were already on this path...) To get the patient medicine info for one patient, you join three tables - the patient table, the medicine table, and the link table that maps the patients to the medicine, as Weng shows in his example select. -Ed --- In [email protected], "Weng Cheng" <[EMAIL PROTECTED]> wrote: > > 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! > ------------------------ 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/
