March 27, 2012 at 4:41 am
This thread is to start a fun project to design a Universal ID Card (UIC) system, based on the following news article from CNN:
http://www.cnn.com/2012/03/26/opinion/frum-identity-cards/index.html?hpt=hp_bn7
In addition to designing the system and database to support a UIC, this thread's purpose will be to give newer DBAs / SQL Devs something to refer to for how project design actually works. Since people can get passionate about certain techniques and procedures in database design, I do ask that everyone remember that there is rarely such a thing as "the one right answer."
The discussion will start with tech specs and business rules. We will assume there is an unlimited budget, and we can have whatever hardware we request. To get us started, here are the things that come to my attention.
Based on the news article above, there are a few things we know:
1) Cards will have ONLY picture and name on front
2) All other personal identifying information (PII) is secured via electronic strip or digital chip and unreadable by the human eye
3) Our system should NOT present PII to the business user
4) As this is a Universal system, with multiple businesses, government agencies, and other customers hooking into it, we can assume our connections / thread counts will be high.
We can make the assumption that the UIC information will be (or should be) encrypted.
What thoughts do you have?
March 27, 2012 at 8:47 am
To start the discussion - Tech Specs Shall we discuss the change or rate of change (now increasing) of the Worlds population. Whose estimates (United Nations forecast, or the U.S. Census Bureau) will we adopt for this project.
For example:
According to the United Nations, ("2010 Revision of the World Population Prospects") world population has reached 7 Billion on October 31, 2011
The US Census Bureau made a lower estimate, for which the 7 billion mark was only reached on March 12, 2012
March 27, 2012 at 9:00 am
Ah, just assign everyone a GUID and make them memorize it. Nobody could possibly be masochistic enough to try to memorize someone else's GUID.
More seriously, GUIDs would be a good key for the whole thing. Plenty to go around, systems to generate them already exist and the algorithms for best-effort uniqueness are readily available. No need to re-invent multiple wheels for this thing.
Names should be stored vertically with a sequencing number, and a "version" or "variation" ID of some sort. That allows people to have multiple names, with metadata tied to usage. So, Marilyn Monroe, aka Norma Jeane Mortenson, would have two entries, one for screen-name, one for birth-name. She might also have entries for married names (I don't know if she took DiMaggio's name, for example, but it might be listed as a "possible use" entry).
We'd need to investigate average storage-size for name substrings. It might be better to have a table of names, and some sort of ID for them, than to store each name raw, or it might not, depending on average binary size and frequency values.
We'd also need a "name variations" table. So that we would have to have multiple entries for "Stephan Smith", and "Steve Smith" (same person), but could have one entry for the prefered value, and allow for variations through a mapping table. There are drawbacks to this concept, but I think the advantages outweigh them.
Beyond that, the needs for the system would depend largely on intended use. If it's meant to be used by people to prove "I am who I say I am", then it has different needs than if it's intended to be used by governments to determine "Are you also known as X?"
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply