How to treat a table that has an alias table as an alias?

Question

I currently have 3 tables:

Drug
  id
  name

DrugAlias
  drug_id
  name

Patient
  first_name
  last_name
  drug_alias_id

The Drug keeps track of the scientific name (ex: acetaminophen) and the DrugAlias, which is a child of a Drug, keeps track of other possible names (ex: aspirin, acephen, aceta, paracetamol, etc.).

The Patient can take one drug, but I'm keeping track of the DrugAlias within the Patient table rather than the Drug because I'd like to be able to remember what the patient likes to call the particular drug. In other words, I'd like to remember that the patient calls acetaminophen aspirin.

The problem is that this doesn't allow the patient to call acetaminophen acetaminophen unless I were to do something like create a DrugAlias with the same name as Drug every time I create a Drug, which creates some duplication.

I was wondering if anyone else had any other solutions to this problem?

Thank you!

Answer 1

Frankly, I think the idea of storing the real name as an alias is an excellent idea.

It simplifies logic and reflects reality - the drug can be called anything in the alias table.

The parent table us for reporting, the child table is for searching. It makes perfect sense to me.

So what if there's a tiny bit of data duplication. To remove the duplication introduces a lot of complexity for virtually no gain. Also there's no issue of data maintenance keeping the scientific name in synch with the same name as an alias, because the scientific name never changes.