Thank you,ed,for the explanation.What you are saying sounds reasonable of course.Having one table with 70 rows is much better than having 70 but one would still have to enter the patients names in these rows. For example, say patient "Mr. John Smith" is on 23 medications and "Mrs Maria Jones" is on 13, then one would still have to have 7o added columns to the "patients" table. Alternatively - which sounds so unreasonable - to add 2000 columns to the "Medicines" table to take care of patients names.
As for the "many-to-many" relationship,I have been searching the net for clues and help and found this nice tutorial which I am stil studying (it mentions VB but it doesnt matter really because the MYSQL part is relatively good .. and I learnt that term from there: http://www.vbmysql.com/articles/vb_mysql_tutorials/vb_mysql_tutorial-part1.html Regards, David --------------------------------- 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! > ___________________________________________________________ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com ------------------------ 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/
