Why are so many men pregnant? Data entry mistakes in the health industry | Hacker News Reader

Why are so many men pregnant? Data entry mistakes in the health industry

straightstatistics.org

22 pointsjmah25 comments

Curious. What's the best method of modeling a relational DB structure to enforce the rule that only:

Gender=F and isPregnant=True

combinations are allowed?

I know application layer can handle this. Also triggers. But is there any RDBMS that allows enforcement at DB level for rules like this?

Yes, it's called a check constraint.

http://en.wikipedia.org/wiki/Check_constraint

Of course!! Many thanks.

Goes to show we hardly use the DB engine to prevent crap data from being persisted!

I blame the Java cargo cult for this. ;-)

While I know the article was about the UK, in the USA a lot of clinical data is not stored in an RDBMS.

The software is written in MUMPS, which has a built in (sort of) document oriented data storage layer.

http://en.wikipedia.org/wiki/MUMPS

You can read the Wikipedia page and look at some sample code and draw your own conclusions on whether that's better or worse.

Thanks for the link.

What a name for a product servicing the healthcare industry. :-(

MUMPS has featured several times on The Daily WTF (here's one: http://thedailywtf.com/Articles/A_Case_of_the_MUMPS.aspx). That alone should tell you something...

This is funny, because MUMPS actually ticks the modern trendy boxes of "NoSQL" and "DSL".

That being said, I run into this kind of thing [crap data being persisted] a lot.

I'm currently working closely with ERP (inventory/job management systems, basically.) and tying into these legacy databases.

It's unfortunate because a lot of vendors store stuff in flat-files with no real relational checks or constraints. This bad data gets pushed to my app with an RDBMS and a schema and all kinds of foreign keys and I end up having to dance around the bad data.

What is so unfortunate is that this data isn't necessarily bad. It's all data you need, it just is out of date or inconsistent for whatever reason. You can't really discard it, but you can't really force it into an RDBMS without a makeover, either.

So what you end up with is a generic import routine that has lots of special cases for each different table that has to deal with inconsistent legacy data. It's a fricken nightmare at times.

Have a table for people and then two tables for 'females' (defined as capable of pregnancy) and 'others'. The two tables link back to the people table with an fkey and each person from the people table has their own row in the applicable table. Then have a 'pregnant' table with an fkey back to 'females' table and a boolean column. Edit: or just the boolean column in 'females' table as danielmiller suggested

This is of course ridiculous so you should just use triggers or application layer, that's how logical data restrictions are meant to be enforced.

If you feel resentful toward your coworkers or are planning on quitting your job soon:

You could have Male, Female, and Transgendered tables, each of which point back to the Patient table. Only the Female table (and perhaps the Transgendered table) would have an isPregnant column.

Lol! But, will this work? How do we ENFORCE mutually exclusive Male, Female & Transgendered records for ONE Patient record?

'Transgender'? You know that's not just one big lump of people? There are trans women and trans men. Also it would inaccurate (and possibliy) illegal to refer to a trans man as anything other than male in some cases (ditto for trans women).

There is a non-zero subset of people out there who will look at any given fixed list of genders and insist they are not any of them, or be offended at being classed as "other" or any artfully disguised equivalent term. Data modelers have to draw the line at some point.

Besides, since we are in medical context, your chosen gender is less important than your physical sex (including all the known intermediates and nons), which is what is actually useful to the medical system. Your chosen gender might as well be a write-in slot for all it matters ("Jedi").

Yes you will often get objections no matter what you say. However in the UK, under the Gender Recognition Act it's a criminal offence to reveal that someone is trans ( http://www.equalityhumanrights.com/advice-and-guidance/your-... ).

Designing a database system, and leaving a criminal liability open like that, is very unprofessional.

Not to mention that Data Protection Law in the EU requires you to store accurate personal data, and allows the person to have this data corrected (i.e. you have to change it if they tell you & can prove it). So you may think "We'll just lump you in the 'Transgendered' Category", however if the person says "I'm male, here's my birth cert showing male", you are legally required to change it.

Agreed!

"Male" isn't one homogenous lump of people; neither is "female". And, as you mentioned, neither is "transgender(ed)"[1].

[1]: http://www.paulinepark.com/2011/03/glaad-is-wrong-on-transge...

It is true that "not all men are the same" (and "not all women are the same"), but splitting into "trans male" and "trans female" is probably more accurate than lumping all "trans people" into one category.

After all, it would be silly to have only 2 chategories "trans" and "cis" (trans:cis :: gay:straight).

[I can't respond to your comment directly. This is a bit off-topic, but I think it's interesting!! Here goes.]

> It is true that "not all men are the same" (and "not all women are the same"), but splitting into "trans male" and "trans female" is probably more accurate than lumping all "trans people" into one category.

> After all, it would be silly to have only 2 chategories "trans" and "cis" (trans:cis :: gay:straight).

My original post was in jest—in fact, it was inspired by some holy-crap-I-can't-believe-it's-real database schema I've had to work with. Someone made an effort to hyper-normalize things and saddled us with a disaster: Every query required seven or eight slow joins, and there was duplicate data sprinkled everywhere (in my example, the same patient could inadvertently have records in both the Male and Female tables). The "architect" quit a few weeks after it went to production.

Anyway, I consider (biological) "sex" to be a sliding scale: one end being female, the other end being male, and the middle being intersex. I consider (social) "gender" to be where one self-identifies on that scale. Others might disagree, but I think this is a useful distinction.

Upon reflection, it's obvious that my schema isn't even remotely helpful! My inclusion of the "Transgendered" table implied that the three tables were genders, not sexes. Gender, being a self-identified trait, has nothing to do with whether one can get pregnant.

So if we wanted to hyper-normalize our schema and indicate that only certain patients can get pregnant, we should clearly have a "Uterus" table. In fact, it would probably be wise to have tables for every body part, and inner-join on all of them whenever we need to grab a patient's information. Or we could use check constraints... but our schema diagrams would be much less impressive.

Check constraints. But DB constraints seems like the wrong level to fix the problem. If it happens often that an non-pregnant person is registered as pregnant, then it might happen with women as well as men. Perhaps even more often! This can't be fixed at the DB level. My guess is that it is some kind of UI issue.

Yes it can. Remember that in any non-trivial system, there is no "UI". There are web apps (there may be dozens of these alone), desktop client-server apps, terminals, interfaces to other systems (which in turn may be many protocols, file formats, etc). There may be 30+ years between the oldest and the newest app, and hundreds or thousands of developers working in everything from COBOL to C++ to Python involved throughout that time. All talking to the same database. You want the same rules to apply to all of them, so have the DB enforce them, job done.

The webdev world, where there is 1 app in 1 language written by 1 team talking to 1 DB and that's as complex as that bit of the system architecture will ever get, doesn't have any techniques that can cope with this kind of environment.

Do you mean that this kind of error can be prevented at the DB level? It is only possible in very trivial cases, like you know a man can't be pregnant. In the DB you can only ensure that data is internally consistent, not that the data entered corresponds to the real world facts, which is really the issue here.

Right, no DB can enforce checks for things that could be true but aren't (e.g. a trigger can't prevent you from wrongly entering an adult woman as pregnant, she might even look pregnant to the eye and not be). But a DB can prevent you doing things that don't make sense (e.g. age must be a positive number, men can't be pregnant, etc etc).

You can do that and that will take care of the "pregnant man" case, but it won't stop a hospital from accidentally recording all births as still births, or other mistakes.

Some things that might seem like mistake aren't mistakes, e.g. people over 18 having pædiatric procedures (e.g. undecended testes is often caught shortly after birth and an orchidopexy is often done in pædiatric wards, but if it was undetected and ignored, for 20 years a 20 year old might need it.).

It's hard to solve a people problem with technical solutions.

Before anyone get's too excited, note that according to a comment on the same article 96% of those men where under 1 week old - and thus legitimately males with midwife episodes.

edit: apologies, that was from a comment on the article.

Also check this: http://blog.xkcd.com/2010/05/06/sex-and-gender/

As a cultural note: hospitals have to code stuff, they are then paid by a Primary Care Trust based on the coding. Thus, there's a financial incentive to mis-code, or to "code harder". This is, obviously, fraud and the NHS fraud unit investigates and punishes it.

The other worry is that this data is potentially used for clinical research, so let's hope people know about the error rates.

Reply on news.ycombinator.com