Text as Primary Key vs Auto Number

[ButtonMoon]

I am gathering the opinion that there are no privacy laws pertaining to a SSN. Neither is there a Law to do with the privacy of your DOB, Driver's License, and Medicare etc.

There is strong advice that one should limit who you give this information to, but no Law. This advice originated from identity thief.

I would think that there is a Law preventing you from sharing my information with a third party.

In Europe, personal data is covered by data protection laws. In general, companies and other organisations can only collect personally identifiable data with your permission and can keep it or share it only for the purposes which they told you about. Anyone keeping personal data has legal responsibilities to ensure it is accurate and protected from disclosure.

http://ec.europa.eu/justice/data-protection/
http://www.ico.org.uk/

The UK equivalent of SSN is the National Insurance (NI) Number, which is mainly used for tax purposes, pensions and claiming state benefits.

 

[Galaxiom]

In Australia we had a referendum and rejected instituting a universal ID system, the Australia Card. So they invented a Tax File Number instead. It is essential to have one to be an employee.

(Nobody asked us about that. It just happened after the Australia Card referendum failed. We all know the tax office is really the most powerful institution in the land.)

However it is illegal for anyone to store the tax file number in any way that is accessible to anyone without a valid reason to know it.

I work in a financial institution. The member has an option to lodge the TFN in the system otherwise they are automatically charged at the highest tax bracket on any interest earned and we give the money to the tax office.

The TFN is one way encrypted and only indicated that a number has been submitted. No employee can ever access the number again. I assume there is some way for the relevant authority to access the number for legitimate purposes.

The TFN must be deleted from any archived forms. But privacy doesn't stop there.

When we record the image of a driver's licence as identification we can record the license number but are obliged to obliterate the Organ Donation status because it is none of our business recording it, even incidentally.

I expect a good many data system developers are ignorant of what they can and can't record.

 

[Anakardian]

In Denmark we currently have a major issue with our version of a person identifying number.
In reality, this number is just a reference to you in a central database that will uniquely identify you.
Unfortunately many public and financial instituions as well as businesses will accept this number as sufficient ID.
This has caused massive problems before and there has been a rising pressure to make the numbers public so as to force anyone to refuse to accept it as an ID. Recently hackers got into the database and got away with a lot of it so it is already semi-public.

The point is, what does such a number prove in reality? All it does is refer to a specific person. Without some other ID it is worthless.

 

[RainLover]

In Europe, personal data is covered by data protection laws. In general, companies and other organisations can only collect personally identifiable data with your permission and can keep it or share it only for the purposes which they told you about. Anyone keeping personal data has legal responsibilities to ensure it is accurate and protected from disclosure.

This is a lot easier to understand. (ButtonMoon's Quote that is) There are no particular laws that relate to the privacy of a particular piece of information. It is all done as a generic set of rules where the onus is put on the collector of the information. I had a problem with a State Department. I did not understand how to apply the Act in my particular circumstance. I was informed that the Department Administers the Law it does not interoperate the Law. So in other words do what you think is right then if the Department does not agree it will prosecute me.

I think that the SSN is like that. You have to decide how to handle it, then if you do something wrong we will have you in court.

So how do we handle things like this when it comes to a Database. First use an Autonumber instead if you can. If you can't do that then get the boss's instructions in writing. Email that instruction to your home address and keep it for the next ten years. You'll never know unless the .... hits the preverable fan.

Sorry about the big ramble.

 

[RainLover]

In Australia we had a referendum and rejected instituting a universal ID system, the Australia Card. So they invented a Tax File Number instead. It is essential to have one to be an employee.

(Nobody asked us about that. It just happened after the Australia Card referendum failed. We all know the tax office is really the most powerful institution in the land.)

However it is illegal for anyone to store the tax file number in any way that is accessible to anyone without a valid reason to know it.

I work in a financial institution. The member has an option to lodge the TFN in the system otherwise they are automatically charged at the highest tax bracket on any interest earned and we give the money to the tax office.

The TFN is one way encrypted and only indicated that a number has been submitted. No employee can ever access the number again. I assume there is some way for the relevant authority to access the number for legitimate purposes.

The TFN must be deleted from any archived forms. But privacy doesn't stop there.

When we record the image of a driver's licence as identification we can record the license number but are obliged to obliterate the Organ Donation status because it is none of our business recording it, even incidentally.

I expect a good many data system developers are ignorant of what they can and can't record.

Galaxiom

I know where you work so it is difficult to argue against you. Are you refering to Sate or Federal laws. North of the Border I have not seen any of the rules you refer to. Namely the average employer keeps a full copy of the Driver's License. National mining companies for one.
The TFN is always kept for payroll purposes. etc.
So why are we at odds here. Surely your State is not that different to hours. Or are these rules comming down from you own Company Lawers.

BTW. I hope you enjoyed the footy tonight. Do you Class yourself as a Queenslander or a New South Welch Man.

 

[Galaxiom]

I know where you work so it is difficult to argue against you. Are you refering to Sate or Federal laws. North of the Border I have not seen any of the rules you refer to. Namely the average employer keeps a full copy of the Driver's License. National mining companies for one.
The TFN is always kept for payroll purposes. etc.

I only know what our internal auditor tells me.

Personally I find it rather bizarre that the Organ Donor status is considered so controversial. Moreover the donor status is blank if no consent is given but this need not be hidden. By inference then a mark covering the status is an indication of consent.

However I am not going to point this out because it would make more work if the auditor decides it need to be fixed. Similarly we have no need to know what kind of vehicles they are licensed to drive but I won't mention that.

An employer holding a TFN is considered legitimate.

So why are we at odds here. Surely your State is not that different to hours. Or are these rules comming down from you own Company Lawers.

I suspect it is the lawyer's prerogative.

BTW. I hope you enjoyed the footy tonight. Do you Class yourself as a Queenslander or a New South Welch Man.

Grew up in Queensland but lived far longer in NSW. Better to back Queensland for obvious reasons.

I was more fascinated by yesterday's "match" in Canberra.

 

[Pat Hartman]

Rain,
If you scan the points in the link I posted, I think you'll see that the Feds have created laws regarding how states can use SSN but that don't apply to their own systems or privately created systems. However, some states have created laws that regulate how private companies can use SSN. Bottom line is - use it at your own risk. My opinion is that it is a bad idea to use it as a PK and if you do, you may end up with a passle of rework if your state decides to restrict its use.

 

[The_Doc_Man]

Back to the subject at hand:

Using text for a PK works but can get long. There are efficiency issues with overly long keys. People's names tend to be unusually long, ugly keys, particularly if your ethnicity comes from Uzbekistan or Kurdistan or Chechnya where your name has entirely too many consonants when transliterated to the Roman alphabets.

But sometimes it doesn't matter so much. For example, in the USA, consider the designated abbreviations of the state names. LA for Louisiana, MO for Missouri, NY for New York, etc. These are fixed, stable, and SHORT. Therefore, it might make sense to use these natural keys as opposed to a synthetic or surrogate key.

On the other hand, there is the question of whether the key needs to be recognizable. If the answer is NO, then a synthetic key works just as well as anything else. For instance, when you do an online transaction and you get back a confirmation number, do you have ANY doubt that the number is the moral equivalent of a very long autonumber key? At least for the bank I use, I've examined that number many times and have never yet seen a pattern to it. It doesn't appear to be encoded. It is just a big, glorified autonumber PK.

 

[RainLover]

This has been an interesting thread.

I don't know enough about US Law so I can't comment too much. I have long thought that using Autonumber to be the better PK, regardless of the contents of the the Table.

Pat I did have another read of your post. I would need more time to gain a proper understanding.

I don't believe I have much more to add to this discussion. TTFN.

 

[ButtonMoon]

I have long thought that using Autonumber to be the better PK, regardless of the contents of the the Table.

Sure, but the example of SSN and its privacy implications under discussion here was about its use as a key for identifying people in the business domain outside the database and not just as an arbitrary key internal to the database: in other words, SSN as a domain key (AKA business key, natural key). You stated earlier:

There is one other rule I apply with Primary Keys and that is that it is never seen by the end user.

So clearly you would not and could not use the autonumber as an alternative for SSN as the key actually in use outside the database. You would presumably use something else (and I would do too). What you designate as the primary key of that table is therefore of no great importance and has no privacy implications. What really matters is what identifiers are exposed to the outside world and how they are used.

 

[RainLover]

So clearly you would not and could not use the autonumber as an alternative for SSN as the key actually in use outside the database. You would presumably use something else (and I would do too). What you designate as the primary key of that table is therefore of no great importance and has no privacy implications. What really matters is what identifiers are exposed to the outside world and how they are used.

All I said was that the Primary Keys should not be seen by the end user.
AutoNumber is good because it has no real life meaning and It can't be duplicated.
It would make no sense to use the SSN as it would be required to be displayed.
The original question here was to do with the use of Autonumber V Text as Primary Key. The discussion expanded to SSN and other Government issued identifiers. The problem with these is more a case of privacy laws, so one needs to decide what is a breach of privacy. Once it is decided that privacy is not an issues then it could be considered for use as a primary key.

I believe AutoNumber to be the best and should be used as the default.

 

[ButtonMoon]

All I said was that the Primary Keys should not be seen by the end user.
AutoNumber is good because it has no real life meaning and It can't be duplicated.
It would make no sense to use the SSN as it would be required to be displayed.
The original question here was to do with the use of Autonumber V Text as Primary Key.

QED. The mention of primary key in the thread subject line is certainly wrong/irrelevant (a case could be made that the phrase "primary key" is nearly always irrelevant in such discussions...) What matters to jk42 and millions of others is a key that can be relied upon and understood by users to identify information uniquely and accurately in the database. The "primary" key becomes irrelevant to that discussion once you assume it will never be seen by the people who need to use an identifier and relate it to physical, practical things in the outside world. A surrogate key that is hidden from users is clearly no substitute for the key used in the business domain - it is something altogether different and orthogonal to it. And since having a natural key is presumably still just as important ever, the advice to "use an autonumber" is IMHO not really much help (however soundly based and well-intentioned that advice may be).

 

[RainLover]

Button Moon

What is it that makes you believe that the primary key must have some meaning to the outside world, as you like to call it.

I thought the purpose of a primary key was to uniquely identify a Record within a table.

You cannot always find a unique Natural key for every table. This forces you to consider a compensate key made up of one or more fields.

The other alternative is to use a surrogate key. AutoNumber is a surrogate key which is suitable in this situation.

I believe you are attempting to apply your knowledge gleaned from other Database designs to Access. Well intentioned but square pegs don’t always fit into round holes.

 

[nanscombe]

Another problem with using a Human readable keys is that data, which would normally be anonymous, in any other table becomes identifiable.

 

[ButtonMoon]

Button Moon

What is it that makes you believe that the primary key must have some meaning to the outside world, as you like to call it.

I thought the purpose of a primary key was to uniquely identify a Record within a table.

You cannot always find a unique Natural key for every table. This forces you to consider a compensate key made up of one or more fields.

The other alternative is to use a surrogate key. AutoNumber is a surrogate key which is suitable in this situation.

I believe you are attempting to apply your knowledge gleaned from other Database designs to Access. Well intentioned but square pegs don’t always fit into round holes.

The purpose of any key is indeed to identify rows uniquely. Assuming the database is intended to model some portion of reality then the users of the database will need some reliable way of relating facts recorded in the database to the real objects, events or concepts that the database describes. If the people who use and update a table can't uniquely identify the information in it or can't accurately map the facts it contains to the real world then there's probably not much chance they will be able to use the database reliably or update it accurately.

Natural keys (the key attributes that will be used in the business domain outside the database) are the things that users depend upon to identify information uniquely. If a database table fails to enforce any natural keys at all then the user is left in the unenviable position of looking at a collection of data potentially containing duplicate rows. From a data integrity perspective such databases are a failure - we've probably all encountered a few of them and had to deal with their consequences.

So what's at issue is the enforcement of uniqueness, not which candidate key of a table you choose as the "primary" one. One key per table is designated a primary key and the others are known as secondary keys or alternate keys. That doesn't make them any less important from the perspective of uniqueness and data integrity however. A surrogate key is no replacement for a natural key because if it is hidden then obviously people can't use it as an identifier and it alone won't prevent duplicate information being entered into the table.

 

[Pat Hartman]

nanscome made a good point which was one of the things I was considering during my argument against the SSN as a primary key but didn't actually verbalize.

@buttonmoon - You aren't going to convince any of us that using SSN (or similar any similar dangerous personal information) as a primary key is a good idea so if this thread is still about that it is time to agree to disagree.

I have no objection to natural keys (text or numeric which was the original question) where they exist and where they are a single column. Once multiple columns are required, they become cumbersome for certain things such as combo and list boxes on forms and I prefer to use a surrogate key at that point and create a unique index to enforce uniqueness for the natural key.

 

[ButtonMoon]

@buttonmoon - You aren't going to convince any of us that using SSN (or similar any similar dangerous personal information) as a primary key is a good idea so if this thread is still about that it is time to agree to disagree.

Maybe I didn't express myself well enough because you are still reading into my posts things that I didn't say. I have no intention to convince anyone to use SSN as a primary key. I agree with what you've said except on one point: you claimed that using SSN as a primary key is in itself a violation of privacy. It is not. The choice of primary key really has nothing to do with privacy. Consider a table with an SSN in it. Is the presence of SSN a violation of privacy? Maybe it is or maybe it isn't depending on who has access to the data. Now suppose I tell you that SSN in that table happens to be the primary key. Does it make any difference to whether privacy is being violated? No it does not.

 

[nanscombe]

But what if the SSN then becomes used as the foreign key in another table, where it has no business to be?

What if the person record relates to a foreign national, who doesn't have an SSN, you can't have a Null value in the Primary Key?

People without a valid American SSN do exist, in fact there are billions of us.

 

[ButtonMoon]

But what if the SSN then becomes used as the foreign key in another table, where it has no business to be?

Then maybe the presence of SSN in that other table would be a violation of privacy. If the SSN attribute is a foreign key but not a primary key in the table then that just doubly underlines what I've already said: the violation of privacy has nothing to do with whether an attribute is a primary key or not.

What if the person record relates to a foreign national, who doesn't have an SSN, you can't have a Null value in the Primary Key?

You can't have a null in any column that doesn't permit nulls, key or not. The question of whether you should permit nulls in a column is a design decision you need to make for every attribute, not just prime attributes. If your database was designed to record information about people who didn't have SSNs then why would you choose to put them in a table with a non-nullable SSN column?

 

[nanscombe]

But if it's a table about real (including non-American) people then logically SSN must be capable of holding multiple Null entries and therefore can not be used as the Primary key.