Duplication of data question

[toffler]

I am contructing an electronic medical record. I have a patient's medication table related the patient demographics table. Of course many patients will be on the same drug. I will have a master drug table which will facilitate rapid entry of medications into a patient's medications table. Now I could use a join file, med_record_# to Drug_ID#. But in this case if the user wanted to enter a drug into the patients medications table (the join table) that was not in the master drug database, he would have to stop and add the drug to the master drug table first. As well, any corruption to the master drug table would affect many patient records. In this case, is it acceptable to have the duplication of data to make the database more secure and easier to use?

Hope this is not a dumb question, but my experience is limited.

Thanks,

Toffler

[Vaughan]

The problem with duplication of data -- such as your drug information -- is that mistakes are so easy to make amongst the duplicated data and then you're buggered when it comes time to get meaningful statistics.

So the compromise is between having to stop and enter new drugs into the master table every now and again, or risk having junk data.

I have designed systems where the client was happy to risk some junk data to get fast and easy data entry: other clients put data integrity first.

It's a good question, and one that is not easy to answer.

[Fenton]

I agree with Vaughan. In this case, considering how important medical information is I'd say junk data is unacceptible, unless someone is going to go through and fix it later, which is time consuming. It is the database designer's job to create a simple-as-possible safe way for them to enter new drugs into the master list.

Or better yet, obtain a huge master list of all possible drugs (up to date, don't know where). Then, if they needed a drug not on their local list, they could go look for it on the big list. This should probably be a filtered search, like "clairvoyance", by typing a few letters (which I do with a portal in a "windoid"; real clairvoyance may be added in FileMaker 8, if rumors are correct; don't know when). I can't see them typing the entire drug name and getting it 100% correct every time; unless they have the drug right there in front of them.

I have a couple example files, in that forum, with the portal choice thing. In the above case there would be 2 possible portals, "back-to-back", the 2nd for searching the big list. If they couldn't find it on either list, they could add it I guess, but that's problematic.

Another method would be to let them type it, if they want, but check that it matches a master list. Don't allow non-matches. The list would have to be comprehensive.

[toffler]

Thanks for the answers. I have found an up to date med database: http://www.e-mds.com/services/icd9/

Thanks for the advice and I have decided to follow it.

Toffler