65536 rows would be if you were using a 16-bit integer as a key. Using a larger number can easily increase this to much larger. 17-bits would give you twice as many rows. Using a 32 bit integer would give you 4 billion something rows. Many companies use mysql for databases containing millions of rows and I wouldn't be surprised to learn that some even have databases containing billions of rows.
Anyway, the point is there's going to be plenty of space for as many patients and medications and their relationships as you could need. Dave On 11/5/05, David Halliday <[EMAIL PROTECTED]> wrote: > Hello Dave, > Sorry about this delay but i wasnt feeling well > yesterday. > > At long last .. it clicked :) It is now very clear > and thank you so much for the detailed reponse. > > Just two remaining points .. I think that MYSQL tables > can have a maximum of about 65000 rows and about 3400 > columns. Now in this particular case .. in theory > only .. 2000 patients could be on 70 drugs each .. > hence the in third table "patient_med", we would have > 70 X 2000 = 140,000 rows. How is this limit dealt with > generally. Could one have several tables .. > patient_med1 .. patient_med2 and so on, and then add > something in the script to check them all? > > And a point about speed, on average .. how long would > it take to check 65000 rows in this case .. > obviously it depends on several factors. I am thinking > really about ways of optimising the script and making > it faster. > > Best regards > David > --------------------------------- > 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. > > > > > ___________________________________________________________ > 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 > > > > > > > > ------------------------ 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/
