In message <[EMAIL PROTECTED]>, emccormick06 <[EMAIL PROTECTED]> writes >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
Surely a combined key field would slow things down (and complicate matters) > - 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. IF you are going to look up patients, and find their medications, then you only need to index the pat_med lookup table on the patient number. If you are going to look up medications, and find which patients take them, you need a second index, on the medication number. The patients table should have an index, and the medications table should have in index as well. Some applications simply don't work with a single index. > 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) And I disagree <G> for the same reason. It's easier if ALL tables have different field names - so I always is 3 letters in front of all fields: patID, patName, medID, medDescription, lkpPatient, lkpMeds So you should have patID, medID, as index fields. Probably lkpPatient, and probably lkpMeds as well. A single index doesn't always work - as here, if you want to look up "meds and the patients that take them" Plus, in this lookup table, you cannot have a unique index. There will be more than one medication for patID 123. But bear in mind that indexes ARE NOT NECESSARY, they simply speed things up, often dramatically. If there isn't an index, the database will create a temporary one each time it is needed, so it wastes time, and just runs slower. > >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. -- Pete Clark http://www.hotcosta.com http://www.spanishholidaybookings.com ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/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/
