Parsing text is a fool's game, but otherwise I may be on thin ice!

Hi,

I have a drug dimension to create. I want a simple flat structure with a header description for drug sets, and a detail description with actual drug names.

My problem is that the drug descriptions exist in a self-referencing table, [presc] which also contains drug sets descriptions, and administration instructions, and sometimes has patient names appended to the front of the descriptions. The parent-child structures can be soft-configured, at the moment they are 1 to 4 deep but could be deeper, and the patient name could appear at any level.

By law I am not allowed to show patient names in the drug dimension because it may be unsecured data, and anyway it would make the drug dimension unusable. If I parse out the patient names, I run the risk of removing parts of real drug descriptions, but I can't leave them in. There is another table [prod] which has clean drug descriptions, but it can only be joined to some of the [presc] rows.

The best I can think of is to use the top level as Header_Desc, using the [prod] desc if it has one, else the [presc] desc. Similarly I use bottom level as Detail_Desc, using the [prod] desc if it has one, else the [presc] desc. Any middle descs get concatentated into another attribute. But patient names might get in. So I have to parse them out if I use the [presc] desc. How to minimise the risk?

Al Wood