Text as Primary Key vs Auto Number

[jk42]

Hi All,

I really just wanted some opinions. When I first was making my database I used all auto numbers for PK's. But then I read an article or two saying that you really don't need to do that if you don't have to (actually, the article said "don't use autonumber if you want anyone to understand your database). I have a few tables (most employee experience type tables) where I just use the word as the PK and there is no auto number in the table at all. Since I'm learning as I go, does anyone see a major problem with that as I continue to build my database?

Just curious.
Thanks!

 

[Isskint]

The role of the PK is to provide a unique ID or reference for each record. Autonumber is the simplest for this as Access takes care of creating the number. If you choose to go down the route of another field type for the PK, it would probably be best to determine a format this will comply with (to ensure conformity and - I think - make indexing easier). Say it is a people table, you might decide the format is Surname & first letter of Forename. With this though, you run the risk of encountering a situation where the PK value you go to enter is already in use so you have to use something else and violate your PK format.

Whilst Autonumber is not Essential, it does make life alot easier. You can always add your own ID field as well as an autonumber for aesthetics.

 

[jdraw]

In general, the Primary key is for use by the database system to ensure each record/relation is unique within a table. If you have some field/construct that does this and is not an autonumber, so be it.

As Isskint has described, make sure whatever you choose satisfies your application - it must be unique for every relation/record in your table.. forever.

The autonumber (or sequence in other database systems) is a convenience to have a meaningless unique number. Since it is unique, it generally satisfies the requirement of PK.

Does that mean you have to use autonumbers? No. But they are convenient; are unique; and satisfy the database management system.

There are several opinions (pro and con) re autonumbers. Just google - surrogate keys.

 

[ButtonMoon]

The only fundamental requirement of a key is that it is a set of attributes which together are irreducibly unique across all the permitted populations of a table (no duplicates or null values). Beyond that basic requirement there are other common sense criteria that ought to guide the selection and design of keys. Three usually desirable criteria are: Simplicity; Familiarity; Stability.

Key generator functions such as "autonumbers" are most often used for surrogate keys and the main reason for creating a surrogate key is to provide a compact and stable identifier where there is no alternative. Auto-generated numbers can't and shouldn't be a universal solution for keys however because databases also have to implement business rules accurately and maintain the unique identifiers used in the business domain. As so often, the wisest answer is "it depends".

 

[jk42]

Thanks to all of you. This does help me. I think I'll be ok. There are only a few tables where I don't use auto number, as it's just easier to use the experience names. Thanks again!

 

[Simon_MT]

The other consideration is the ease of input, I have 151 types of Medium using Oil (Paint); 52 Acrylic so I use Short Keys because it is quciker than scrolling down all the variations. OOPN is Oil on Panel is is 104 in the list starting with O. OOPN involves four keystrokes.

Simon

 

[ButtonMoon]

So, for folks in the US, SSN is a typical candidate key. It is a violation of privacy to use SSN as a primary key so you will always use a surrogate key. But you still need a unique index on SSN to ensure there are no duplicates.

Hi Pat,

Privacy is unrelated to the choice of keys. Privacy and security are concerned with how data is managed and used. If you expose data to unauthorised people or fail to secure it properly then that is a potential violation of privacy irrespective of whether it is designated a primary key or not.

It might sometimes be that the technology presents certain obstacles to securing key attributes vs non-key attributes (replication for example), but in all cases it is the methods you use to secure and manage the data that addresses privacy, not the choice of keys.

 

[ButtonMoon]

Pat,

I entirely agree on the importance of privacy and that SSN shouldn't be exposed and put at risk of disclosure. That has nothing to do with whether it's a key or not, much less whether it happens to be a primary key. Just because you enforce the uniqueness of an SSN that doesn't mean you should make it any less secure - it just means you care about data integrity. Wouldn't you agree?

 

[Galaxiom]

Pat,

I entirely agree on the importance of privacy and that SSN shouldn't be exposed and put at risk of disclosure. That has nothing to do with whether it's a key or not, much less whether it happens to be a primary key. Just because you enforce the uniqueness of an SSN that doesn't mean you should make it any less secure - it just means you care about data integrity. Wouldn't you agree?

No. A PK that requires encryption to meet regulations is a rather ugly concept.

 

[ButtonMoon]

A PK that requires encryption to meet regulations is a rather ugly concept.

Not at all. In very many cases entire databases are encrypted. Perhaps you haven't worked with such systems but I can promise you there is nothing ugly about them. I seriously doubt that there are regulations that apply only to data when used as a key in a table - the data values themselves are what matter to regulators. And if you want to ensure that encrypted SSNs are unique (as Pat suggested) then you'll need a key enforcement mechanism that works for encrypted data.

Encryption in any case has very little to do with the general concept of security in a database context because the gatekeeper to data is usually the DBMSs own authentication mechanism. Typically the same mechanism is used whether data is encrypted or not.

I think Pat's point is really about disclosure - unnecessarily exposing the SSN outside the database. However, it's extremely common to have keys that aren't exposed to all business users. Different users may use different identifiers for employees depending on the context. For example the login name, payroll number, badge number or surrogate key might all be alternative keys for employees. The question of which key might be designated "primary" by a database designer is uninteresting to business users and to regulators. What matters is what keys are used by whom and in what real world context.

 

[ButtonMoon]

Pat,

That key values change is a fact of reality. If you think you have a key that will never need to change then you are probably wrong. Stability is a sensible basis on which to select a key but immutability is irrelevant, often unachievable and frequently impossible to verify.

Should we not enforce keys if we expect their values to change? Of course we should still enforce them. The fact that key values may change is at least as good a reason to implement a key as not because if the key constraint is not enforced then there may be a greater risk that a changing value could unintentionally violate uniqueness.

My main disagreement though was with the comment you never explained - that using SSN as a primary key is somehow a "violation of privacy". It is not in any sense that I'm aware of. I think that's a far more important point.

 

[ButtonMoon]

The example I gave you of the Medicare card is an example of a privacy violation.

It is indeed but it has nothing to do with whether the SSN is a primary key or not; it's simply an example of careless use of personal data.

Suppose the designers of the Medicare database had followed your advice and designated something else as primary key but they kept SSN as an alternate key and still printed it on the Medicare cards. That would amount to the same violation of privacy. Now suppose instead that they made SSN a primary key but they printed some alternate key value on the cards - the SSN only being used inside their systems for internal administrative purposes. The violation of privacy you described would no longer exist. The choice of primary key is irrelevant as far as privacy is concerned. What matters is how the keys (potentially any number of different keys) are used in the outside world.

Generally speaking, where several alternative keys are in use the choice of which one should be "primary" is essentially arbitrary (that was E.F.Codd's opinion, not just mine) and a matter of perspective. In practice a "primary" key is often chosen for reasons that have nothing to do with what is the "preferred" or most widely used identifier.

 

[RainLover]

I believe in following a Standard. Yours may be different to mine but whatever it is it should be followed 100% of the time if possible. We can all follow the standard of not using Reserved Words or not using spaces (And other non Alpha Numeric characters) but it is possible to use either. My preference is to select the one that can be used all the time. In these cases only not using Reserved Words and not using spaces (And other non Alpha Numeric characters) are the only two that can be enforced.

The same applies to AutoNumber V Natural Values. AutoNumber can always be used where Natural Values may not. In the case of Natural Values you may have to introduce a Second or Third or Fourth Field to obtain a unique value.

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

These are the rules I have set myself and I stick by them.

 

[RainLover]

Now for a quick question about your SSN. Is this privacy thing that I see so often, set down by law or is this someone's interpretation of some privacy law.

Then when we are deciding who should have access to that number how is that done. (Namely who gets Access)

 

[RainLover]

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.

I must add that I am not American and my knowledge of US Laws stems from watching American TV shows and Movies. However we do have similar problems here in OZ so it follows that we both have similar Laws to overcome these problems.

If I am correct then a reassessment of your stored data could be warranted.

 

[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.