November 30, 2016 at 3:27 am
My database has two tables relating to patients. The first holds static data such as first and last name, DOB and NHS number and the second dynamic data such as height, weight (from which BMI is calculated) and employment. The input screen(s) written in wxPython carry degree of validation which ask for confirmation if information is to be added/updated that falls outside the normal range of expected values (e.g. if height is over 2.1m). Various issues have been addressed over the years but I now have one that is less straightforward.
The name field validation was implemented in line with passport office guideline, i.e. the name cannot contain numbers, symbols and punctuation marks apart from hyphens or apostrophes. There is also a rule that each name must be at least two characters long. This has never been a problem until this month when one clinic decided it wanted to include numbers in the name fields. The “why” logic seems unclear. I have now been asked to implement this but cannot think of the best way. If I just allow numbers there are likely to be issues with other clients using typically 1 and 4 in error for I and A! However the client who wants to use numbers does not want to confirm every entry. Obviously this is not a true database design issue but I am looking for comments particularly from anyone who has encountered this problem.
Many thanks…
November 30, 2016 at 3:43 am
Is the numeric part always in the same place (i.e. end of the forename/start of surname or suchlike)?
If so, it might be worth stripping the numeric section off into a separate column and concatenating them when required - the name might be used later elsewhere, for reporting purposes and the numbers would make it look odd on a report.
Also, is it possible to identify which clinic creates the data? If so, you could narrow down the validation so numbers can only be used when a specified clinic has input the data.
Although not my favourite feature, a trigger would work well for this.
November 30, 2016 at 4:02 am
mjh 45389 (11/30/2016)
I have now been asked to implement this
What have you been asked to do - fix the incorrect data, or prevent any further incorrect data from getting in the database? If you're fixing, use PATINDEX and STUFF. If you're preventing, create a check constraint on the Name column that verifies the value is not like '%[0-9]%' (for numbers - punctuation marks are going to be a bit messier but still doable).
John
November 30, 2016 at 4:25 am
I have been asked to allow numbers in the names. At first I thought that this was to cope with typically multiple John Smiths, e.g. John Smith27. This is doable as you say. In fact additional fields such as DOB and (optional) NHS Number make a person unique.
However when I sought more clarity it turns out that this clinic holds the proper/correct(?) information on another system. This system then converts the names into letters and numbers which they wish to use on our system with a default DOB. Their IT police cite data protection, etc. but I wonder how clever their algorithm is? One similar implementation I encountered a few years ago did not need an Enigma machine to crack it as it was akin to trivial encryption I did in cub scouts in the sixties. Opps, showing my age! 🙂
November 30, 2016 at 4:37 am
Your original request was for comments from those who have encountered this problem before. I haven't, but I'd say all you need to do is make any necessary changes to data type of and constraints on the Name column in order to allow it to accept the obfuscated names that the algorithm generates. You'll probably also want a unique constraint on the column, just to check that the algorithm doesn't generate the same alphanumeric soup more than once.
John
November 30, 2016 at 12:54 pm
Does each client have a database specific to that client, or is there a single database that contains data from all clients? Does the table that contains the name column(s) also contain the clientid for whom that is a patient?
If there is a separate database for each client it looks straightforward - just have a different validation according to which client's database it is.
If it's a database shared between clients and the table that includes the name doesn't also identify the client it is difficult (it will need someting like a stored procedure that does the necessary checking), but if it does identify the client it's easy - just use a different validation for the name column(s) according to which client is identified in the row (easy to do with a check constraint which accepts good names from all and bad names from special clients - if more clients want this, you will want a table that says which clients want this in order to make that check constraint non-verbose).
Tom
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply