Hello David and thank you very much for your response
too but there is something * SO OBVIOUS* to you and I
just cant see it.
How on earth could table 3 .. the patient_med table ..
do the matching??Honestly now.. there is something Im
missing.. sorry to trouble you but look you are saying
that the first table contains nothing about
medications AND the second table contains nothing
about patients and then the 3rd table, by some magic
or crystall ball reading ?? will tell the user that
Joe is taking Aspirin and Advil while
Susie is on Codeine. Honestly I am missing something
here .. I mean where from is it getting these results
if the two first tables are completely separate
entities. It is not my day today :)
---------------------------------
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!
>
>
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.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/
