- Feature Articles
- CodeSOD
- Error'd
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Sounds like a requirements WTF.
We've all been there before clamoring with the 'but what about ...' only to be shot down and labeled a complainer. I'm sure afterwards the developers baked some 'I told you so' cake, but it would be there fault regardless.
Oh Progress...
Admin
That is a problem. My insurance agent by pure chance had two clients with the same (and unusual) first and last names, and identical birthdates. Their computer kept denying one of them coverage because the other one had already had that operation.
Admin
No testing for twins, triplets etc eh?
Admin
Don't worry, the real WTF will come when he actually tries to make a claim !
Admin
Two children born a month apart is more likely than twins! Woo overlapping pregnancies!
Admin
Heh. In all my extended family birthdays are clustered about 45% in May, 45% in July, and 10% around the year. Sun Life would just explode if they handled us I guess. Whole households with the same last name and same month of birth (and sometimes the same year).
Admin
As often as it is NOT required, I almost always opt for a Unique "autonumber" ID field.
Admin
Where I live (Iceland) everyone gets a unique id assigned to them at birth that lives forever in a national registry. This is used practially in every computer system where you need to ensure that you can identify people uniquely.
Gotta love Big Brother.
Admin
Admin
Hah, it's not like this is a specialised field, it should have been one of the first design thoughts - twins, triplets. The addition of a single character to the lookup would have been enough to differentiate dozens of otherwise equivalent siblings.
What happens when the one with the wrong date needs to claim (okay, they're babies right now), and gives the correct birthdate? Does the other twin have it levied against him? "Sir, it appears that one of your children is twice as ill on average as other babies, we will have to raise your cover".
Admin
Admin
G@d help the John Smiths of the world!
Admin
sheesh... talk about designing databases with unique primary keys... when will the concept get through that it is always advisable to have a technical primary key (auto-increment field or the like) AND a business primary key (and a real one, at that, not based on name and day of birth only....) social security numbers here e.g. consist of DDMMYYYYXXXX, namely the date of birth (with the year in 4 digits ;o) and a 4 digit number. taking the number of inhabitants into account (8 million) this should always suffice :o)
captcha: alarm - so true ;o)
Admin
Actually you have to admit that would make our jobs as programmers much less complicated in many cases. If the United States just automatically assigned a social security number at birth it would be practically the same thing.
Admin
The best bit is, insurance companies share information "to prevent fraud". If in the future this gets checked it's going to look mighty dodgy
Admin
Maybe I am crazy, but wouldn't using the SSN be the most glaringly obvious choice? It is unique for every American citizen. That way, you could have as many John Smiths born on 4/15/1970 as you wanted, their SSN would tell them apart. I just don't understand why that wasn't the obvious choice when developing the software.
(I understand with babies it may take a while to get the SSN, but certainly the insurance company can wait a little while.)
Admin
This is more common than you think.
I had twins 7 months ago. After receiving the certificate of registration from the city, I promptly went on-line to get an Ontario birth certificate. I filled out the form, paid via credit card. 1 week later, I received a birth certificate for one child, and a rejection notice for the other (birthdate problem). I still haven't receive the second certificate.
Admin
They would have been able to handle the twins had they used a boolean primary key.
Even triplets if it was a TdwtfBoolean column.
Admin
Duplicate prevention in social security numbers is horribly flawed. This is one of the reasons that identity theft fraud is so easy.
For example, there are whole industries (criminal) that do nothing more than sell legal social security numbers to illegal immigrants. This has even been in the media a few times.
Equifax, Experian and TransUnion, the major credit rating companies, even have procedures in place so that your credit record is filtered by social security number and by name. If someone else has your social security number, you won't see their credit activity on your credit report, and vice versa. Unless you both have the same name. Thus, one of the things to watch for on your credit report is addresses you've never lived at, because that can indicate someone else has your same number, and you need to take action to handle that.
So, while social security number seems like a valid primary key/unique index for a database, it really isn't enough.
Admin
Admin
Admin
SSNs aren't as perfect as they seem. For very secure applications, most people will consent to giving their SSN, but I'm not going to give joeblow.com my SSN to register for a forum.
They have issued duplicate SSNs before. Since people take it at face value that they are unique, there would be no mitigation factors and someone would be screwed.
Admin
My brother and I both had new baby girls join our families in 2005. Despite living hours apart and rarely speaking, we discovered during the pregnancies that we had chosen exactly the same first and middle names. We didn't really want them to have the same name and they were born two months apart, but we also thought that as rare as our last name is that they would be suspected of being duplicates in every computer system on the planet if one of us didn't choose a different name.
Having wrangled with insurance companies many times, this kind of thing doesn't surprise me at all.
Admin
I can understand that when you design a system, you can overlook certain extremely rare situations. Just because they don't happen alot, but twins... Common?
Admin
I had a similar problem with my daughter - she was born on Jan. 1. We've come across THREE separate software systems that reject Jan. 1 as a valid birth date. I can't POSSIBLY imagine how that got in there.
Admin
It's also not required to have an SSN in the U.S.
Admin
Yep... I have twins, and probably 1 out of 5 times when they both go to the same doctor on the same day, the claim gets denied or mishandled.
capcha: craaazy (How did they know??)
Admin
Oh course we do! It's a Social Insurance Number (SIN). Why they don't use it is anybody's guess, but sometimes people are always walking on eggshells being worried about the privacy implications of storing that number. In this case, it's completely stupid cause they require it anyway. sigh
Admin
That's a great idea for a cake, I was just trying to decide what to make next week. Excellent. I'll bring it in to the next SENG meeting.
As for the actual WTF, it's definitely a requirements thing. They could've at least used their SIN or something, but the month of birth? Sorry, not unique enough.
Admin
SSN's are definitely not unique. I had to change my SSN when the IRS got involved after I started working - because someone else living in another state was reporting income under my SSN (and filing a 2nd tax return). This was not an illegal immigrant or anything like that, it was just a young american lady who had no idea why she was getting crap from the IRS either.
Admin
Sure do. It's a SIN: Social Insurance Number. Still not a good choice for a primary key (as mentioned above), but it's probably better than what they've got. Even if they didn't want to use an autoincrement column, you'd think they would at least have the fields for the policy participants labelled A,B,C,etc.. Then all claims could be sent in as policy#-A or policy#-B..
Admin
This is not a matter of primary keys here, this is a constraint rule set on the database.
Admin
Unfortunately, that's not totally true. Even ignoring all the cases of identity theft, there have been thousands of cases where duplicate Social Security numbers were assigned. Besides, there's no legal requirement for a US citizen to get one.
Admin
What about adoptions? You could end up with any number of children with identical names and/or birthdates!
Admin
Admin
When I joined the Air Force and made my way to tech school, I ended up with a roommate. We both signed up for the military on the same day and arrived at basic training on the same day as well as in the same flight (group) and arrived at the same tech school at the same time.
The issue arises that one of us had to be of higher rank. For what reason, I haven't a clue. We both were E-1's (lowest rank possible).
Okay, so which one of us is older? We were both born on the same day and year!
So what next? Our first names are the same.
Then what? His last name started with a "T" and mine started with a "W".
He out-ranked me with his last name! NUTS! ;/
Admin
Haha, they sent you a rejection notice? Guess you have to send that kid back...
Admin
It seems to me, based on the constraints, that if two people born in the same month of the same year were to get married, and one took the other's last name, they would not be able to be on the same plan either...
Admin
that company should ask to every pregnant to make their best effort to 'release' the twins on midnight.
the insurance system will then be able to register the first birth on the first day and the second on the following day !
Of course triplets (or more) are a bit more difficult to handle .....
Admin
Why not organize by SS#/tax number like everyone else?
And could you imagine having twins with a month-long labor to separate them? My wife would've either murdered me or the doctor!
Admin
Yeah, I'm a twin myself. I had divorced parents who both used Delta Dental for their dental insurance. They could never in the 14 years or so that I lived with my parents and they had that insurance figure out the difference between my brother and I. It became worse when I moved in with my dad and they half the time assumed that my brother was going to my dentist and I was going to his dentist. The best was when I had my wisdom teeth removed because they thought that I had already had them removed once (obviously it was my brother) and nearly denied the claim.
Admin
E.g. You need a SIN to work in Canada, so for foreigners on work visas, they typically get a temporary SIN. You can imagine all SIN's will get recycled eventually, with the temporary ones more frequently, so using one for a primary key is an accident waiting to happen.
IIRC in Canada the only people you're required to give it to are banks and employers.
Admin
I don't know how it works in the US, but in Canada, you don't get a SIN until you apply for one. My two boys don't have one yet because the freaking province won't give me a birth certificate as mentioned earlier. Funnily enough, they issued temporary health cards within hours of birth.
The real wtf is the fact that I've been on hold with the Registrar General since my previous post.
Admin
This story is completly BS. I used to be a developer at Sun Life Canada, and this was never an issue (unless if it happened within the last year, or a very long time ago). They have an excellent source control prosses, as well as many development and test servers. Any single change had to go threw a large change request approval system Unless this story is referring to another Financial company (which I can't see why it would because why would you harm one companies reputation for the sake of anothers) I call it BS. Sunlife has Millions of customers across the World, with Im sure many "Twins" on the same plan. How exactly would the old data (that would of had Twins in it) exactly make it into the new System?
So unless this story happened many years ago, I call BS on this wtf, either that or the CSR was new and absolutly had no idea how to use the system (very possible)
Now while this story is indeed interesting, after reading it it makes me question the integrity of every other WTF posted.
Admin
Sunlife does not require childrens names or birthdates. Liarface.
Admin
Hey! I'm your evil twin.
Admin
In Italy, there is the "fiscal code", that is an hash based on sex, birthdate, birth location, name and surname, and has a rule to solve hash collisions. Unfortunately some programs can accept only non collided hashes, or worse reshash the code on the fly and use it instead the official code, even if it's a misdemeanor.
Admin
"wouldn't using the SSN be the most glaringly obvious choice? It is unique for every American citizen."
No it isn't. People only think it is.
The SSN was never intended as a personal ID number. In fact, it's not (or at least originally wasn't) technically legal to use it for anything other than social security purposes (IANAL), even though people keep doing it anyway. In an attempt to discourage such use the government deliberately introduced duplicate SSNs, but most people never knew that so it keeps getting used as if it were unique.
Driver's liscence numbers are unique (at least if you take into account the state - I'm not sure if there might be duplicates across state lines), but the obvious problem with those is that not everyone has a driver's liscence.
But this story doesn't surprise me in the least. The entire medical insurance industry is just one giant WTF.
Admin
The correct way would be for the insurance company to create a unique in-house ID number for every person that they are insuring, and to use that instead. The ID number would be global across their entire system.
A lot of database people think that a primary key should always be composed of "natural data" and not to have unique ID number columns. This is an example of why they are wrong, because there is no set of natural data that will always uniquely identify individual people.
Admin
We have a Social Insurance Number. I think it's the same as the SSN