Pete, thanks for your informed response - I joined this list because I'm hoping to improve my chops by interacting with guys like you. Let me explain one of the points below, since I think you misunderstood my intention. It's quite likely you'll ALSO disagree with my actual intention as well - in fact, I'll predict it - but we might as well be sure.
I have an important thing I want to say at the begining. It's so important, I'll repeat it at the end: I strongly suspect that Pete is a better and more experienced LAMP programmer than I am, and in general it's more likely that he's right and I'm wrong than the other way around. However, what he's saying is different from what I currently believe, so I'm going to try to explain why I believe what I currently do, so that (probably) he or somebody else can explain why I'm wrong in why that I understand, or (less likely) maybe I'm at least partially right. Additionally, to keep this from getting tedious, I'm going to express my side as if I believe I'm right; but if it sounds like I'm dissing Pete at all, please know that I'm not. Ok, on with it! To summarize what we said below: - I suggested a technique I use to make it easier for DBAs to manage lookup tables, and that was to put a "comment" field that allows you to tell which rows in the original table are addresses by a row in the lookup table. - Pete had three objections: 1) he didn't like the idea of having to manually enter the comment field 2) you'd have to edit the comment field if the source data changed. Let me address both of these by explaining the idea of the comment field: it's a comment, not data. It doesn't HAVE to be complete or accurate. That sounds like heresy to a db guy, and you wouldn't do this at an organization where you're sharing your code with a lot of people. If you're flying alone though, or sharing with a small number of people (as in, everybody who ever looks at the code has your number on "speed dial") then a comment is useful if it's accurately filled in just MOST of the time. Let me give you an example. Ms smith is being taken off of aspirin and put on AspPlus. Obviously, you wouldn't change the row devoted to Apirin to AspPlus (as somewhat strangely you implied in your message), you would add a row for AspPlus, add a row to the lookup table relating Ms Smith to AspPlus, and nuke the original row relating her to Apirin. You don't even have to do this as an atomic operation, since they're not actually related - you're not really "changing" her aspirin to AspPlus (to give a visual: nobody goes to her room and replaces the pills sitting in her aspirin bottle!). You're ending her aspirin plan, and starting an AspPlus one. Anyway, adding the new relation was easy. You fire up the admin screen you wrote (you're not doing this day in and day out editing raw SQL, obviously). First you choose the "add medicine" screen and type in the info for "AspPlus". Then you go to the "assign medicine to patient screen", select "ms smith" from one drop-down menu and "AspPlus" from another one. The PHP code dutifully creates a new row in med_patient, with Ms Smith's patient ID and AspPlus's med id. It also helpfully adds the comment "smith - aspPlus" to the comment field (note: automatically, by PHP code; no manual data entry involved). So now you go to stop the asprin assignment. You go to the "delete medicine assignment" screen and - oh no, it's not working. Maybe someone changed a library you needed. Maybe you haven't finished it. Do you call Ms smith and tell her she can't switch the medicine until you finish updating that admin screen? "I should have that for you by the end of the month". No, of course not. Even if you're going to drop everything you're doing and finish that screen, you're STILL going to manually type in the SQL to delete the row in question first. Now, if you didn't have the "comment" field, you have these steps: - find ms smith in the patient table - write down her ID - find aspirin in the med table - write down its ID - do a delete with those two IDs. - go back and confirm that ms smith is no longer on aspirin, using the regular administrative interface. Here's how it goes if you DO have the comment field: - Look down the med_patient table (which you have sorted on the comment field) for smith-aspirin. (if you don't see it, do it the "old way" above; that's the cost if the comment field is missing for whatever reason) - Having found the row that has "smith-aspirin" in the comment, do a select on the med and patient ids you have there. This is your check - it should show ms smith and aspirin. - delete that row, using it's ID. - go back and confirm that ms smith is no longer on aspirin, using the regular administrative interface. The MAIN reason I prefer the second way is that it makes things a LOT easier (at least, it seems to me) when you're using a tool like phpMyAdmin. You don't have to type any raw sql commands, you can just click on the different tables to see what's going on. And you can do it quickly and accurately. It makes less of a difference if you're NOT using a graphical db manager, but then...why aren't you? A few caveats: the sites I work with are mostly marketing sites; no e-commerce, no money changing hands, nobody's going to die if something screws up. As such, I'm aware that I'm sacrificing a certain amount of safety and reliability for speed and convenience. This is NOT something I'm doing lightly or sloppily, it's a conscious tradeoff: I'm saving a LOT of time for a SMALL reduction in reliability. It most cases, if I had to do it in a more "expensive" way, I couldn't afford (the clients couldn't afford") to do it at all. Note that the particular example we're using is NOT the kind of situation where I would skimp! I hope the original poster is not putting his code in life-or-death situations at his level of experience. And to reiterate my opening remark: strongly suspect that Pete is a better and more experienced LAMP programmer than I am, and in general it's more likely that he's right and I'm wrong than the other way around. However, one doesn't get better if you say "I understand" when you don't! There are very real time-saving advantages that come from the "comment-field-in-link-tables" technique (pattern?) I suggested above. (it may not seem like a lot reading one example, but do it for 20 patients, and it's the difference between 20 minutes and two hours). I think I've addresses the major complaint Pete rased (extra data entry time) but I look forward to other opinions, observations, or criticisms. Or who knows, even compliments... Ed --- In [email protected], Pete <[EMAIL PROTECTED]> wrote: [...snip...] > >3) This last one is just my own technique, I don't know if other people > >do this, or object to it, > > I choose to 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 > > You are creating extra work at dataenty time - and (to me) it is a > Golden Rule that no data should EVER be entered twice. > > In the example above, supposing Ms Smith gets married, and you need to > change her name to Brown. Are you suggesting that all the lookup > entries should be re-typed? > > Supposing Aspirin is no longer supplied, and all patients are moved to > the new wonder drug, AspPlus, are you suggesting that the table should > be re-typed? > > If you are the DBA, it should be almost second nature to type in the > SELECT patName FROM patients WHERE patID=320; > or > SELECT medDesc FROM meds WHERE medD=12; > > Or perhaps you are lucky enough to have MySQL5 - doesn't it have views? > - I haven't tried it yet. ------------------------ 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/
