Ok David, now that you understand how the lookup table works (and take a moment to appreciate the beauty and elegance of it - isn't it cool?) I wanted to add 3 little bits of advice that I think will help you at this stage in your development into a SQL master.
Note that these 3 thoughts are my own creation, and run a little bit against the conventional wisdom. People may chime in to disagree with me on some or all of them; nevertheless, I think you'll find it helpful to at least consider them. 1) Remember this: databases are incredibly powerful programs that very, very smart people have worked a long long time on. "That's obvious, what's your point?" you say? Here's why I remind you of that: databases are primarily designed to store and recover specific bits of info from large datasets, and they do that VERY VERY well. Don't be afraid to ask the DB to do something that seems intuitively to be "hard" - you'll often be surprised at how fast it can do things. That doesn't mean that ANY question you ask it will be answered quickly - we can all show you ways to bog down any database. The key point is to not ASSUME that something will be slow. Try it first! Only worry about speed if it's not already fast. (I bring this up because you were worried about speed in your example with 2k patients and 70 meds. To MySQL, that is TINY. It's 2+2. It'll spit that out so fast you won't have time to blink) 2) Here's where I'll get a little controversial: one of the previous respondents suggested making the lookup table have two keys, using both the patient id and the med id as a combined key field. While you can do this - and really sophisticated DB designers probably prefer it - I would suggest you do what the first guy originally suggested, and give the lookup table it's own ID. In general, until you reach a HIGH level of SQL sophistication, it's just easiest to always have a single, indexed, unique id field. I'd even suggest ALWAYS naming it "ID". It's just one less thing to get confused about, it gives you a consistent "handle" to manipulate a row (to update or delete it, for example) 3) This last one is just my own technique, I don't know if other people do this, or object to it, but here goes. The problem with lookup tables (the patient_med table in your case) is that they are very confusing to look at alone, since they're just numbers. Obviously, you'll want and need to design admin screens to enter, retrieve, and edit data in them, but even so, I often find myself looking at the tables in phpMyAdmin or other similar tools. So....here's my trick: I add a field (usually called "comment") that's just for me, in which I put a little data from the other tables in so that I can tell what they are. Note: Be sure you don't actually USE this for anything other than reference, since it's duplicate info. But if you're the DBA, it makes it easier to figure out what's going on. Here's an example: If your patient row is: ID: 240 | Firstname: John | Lastname: Smith and your med row is: ID: 12 | medname: aspirin then your lookup row would like like this: ID: 320 | patient_id: 240 | med_id: 12 |comment: Smith Aspirin Ok, that's my $0.02 (for a total of $0.04 I've supplied so far :-). None of these are authoritative, but perhaps they'll be useful to you. --- In [email protected], David Halliday <[EMAIL PROTECTED]> wrote: > > Thank you Pete. > > It is now clear and fixed. > > > What I can't get clear is, what you will (are able to > / are allow to) > do. It seems that you don't want to do any data > shuffling to put the > data right? You just want to add one field to the > Patients table? And > you don't have a code number for the drugs, just a > tablename. Do you > appreciate that you would build table3 with SQL > commands, not by typing? > > You see, I don't understand why you have this problem. > I can see that, > historically, the database was designed badly, and > needs putting right. > But you seem to be saying that you cannot (are not > allowed to?) put it > right. Or is it just that you don't want to? > > > > You see the point of creating a third table totally > escaped my mind - and its a fact of life that people > have different IQs ;) > > At first I didnt know that it was intended as a > "look-up" table etc.. But now its crystal clear.. > obviously its the logical and correct way of doing it. > > Thank you for your contribution. > Best regards > David > > > > > > > ___________________________________________________________ > Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with > voicemail http:// uk.messenger.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/
