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! > > > > > The php_mysql group is dedicated to learn more about > the PHP/MySQL web database possibilities through group > learning. > > > > --------------------------------- > YAHOO! GROUPS LINKS > > > Visit your group "php_mysql" on the web. > > To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > Your use of Yahoo! Groups is subject to the Yahoo! > Terms of Service. > > > --------------------------------- > > > > > > > ___________________________________________________________ > 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. > Yahoo! Groups Links > > > > > > > [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income households are not online. Help bridge the digital divide today! http://us.click.yahoo.com/cd_AJB/QnQLAA/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/
