Is it not a good practice to display primary key on UI?

Badam123

Hi All

Presently we get device details though a feed where multiple devices can be mapped to single CustomerId. The device details are loaded in table : TBL_DEVICES . The devices presently do not have any natural primary key so we have created a sequence as primary key . We had a new request to assign a unique key for each device so that they can be searched from the UI using the unique key (Presently they can search using CustomerId) . The team has asked me to create a new column and increment using a sequence .

TBL_DEVICES  ( Existing structure)

-----------------

DEVICE_ID         NUMBER  PRIMARY KEY using Sequence

CUSTOMER_ID  NUMBER  FOREIGN KEY

DEVICE_DESC  VARCHAR2(100) 

CAPACITY         NUMBER

Can't we use the existing primary key as a unique key? . When I asked the same they said that showing primary key on UI is not a good practice . I was not convinced as it doesn't make sense to add a column and sequence which adds no value.

Please suggest.

Thanks

John Thorton

3372439 wrote:When I asked the same they said that showing primary key on UI is not a good practice .

Who are "they"?

Ask them for proof & substantiation to support their claim.

What is downside for showing PK?

BEDE

Still, I think the device should have some unique identifier, other than the ID generated using the sequence. It should have some kind of serial number or so... Otherwise, there is the risk of entering the very same device N times and losing it completely.

GregV

Hi,

I don't see a problem in showing the pk id. Can you ask "them" why it's not a good practice?

mathguy

There are cases when you may want to hide a PK, even if the PK is generated by a sequence. See how close your situation is to these examples. (I don't see, in particular, how such things might apply to id's assigned to devices, not to people or to financial assets.)

1. Personal identification number/code (in the U.S., Social Security Number). At least in some countries, identity theft is a big concern, and the most valuable piece of information for thieves is the PIN (SSN in the U.S.). One might make the case that SSN's are generated by sequence (with a number of rules though). And some databases do use the SSN as primary key. I would not want that PK to appear in the UI, if the UI is to be accessed by users who don't otherwise have access to the PIN/PIC/SSN.

2. Same with bank or brokerage account numbers, mapping to customer id. Right now your users can access information by customer id. They may need to be able to check information from one account (say from the "joint checking account") but they have no business knowing the actual account number. Account numbers, again, are probably generated by sequence. I wouldn't want to expose those numbers to the "general public" (depending on who, exactly, has access to the UI).

GregV

Hi Mathguy,

I think in both your examples these wouldn't be used as PKs. Suppose I make a mistake in entering somebody's SSN or bank account number, then the PK would need to be updated.

John Thorton

3372439 wrote:Hi AllPresently we get device details though a feed where multiple devices can be mapped to single CustomerId. The device details are loaded in table : TBL_DEVICES . The devices presently do not have any natural primary key so we have created a sequence as primary key . We had a new request to assign a unique key for each device so that they can be searched from the UI using the unique key (Presently they can search using CustomerId) . The team has asked me to create a new column and increment using a sequence . TBL_DEVICES ( Existing structure)-----------------DEVICE_ID NUMBER PRIMARY KEY using SequenceCUSTOMER_ID NUMBER FOREIGN KEYDEVICE_DESC VARCHAR2(100) CAPACITY NUMBERCan't we use the existing primary key as a unique key? . When I asked the same they said that showing primary key on UI is not a good practice . I was not convinced as it doesn't make sense to add a column and sequence which adds no value.Please suggest.Thanks

Implicit in using SEQUENCE as PK, is that only a single record will exist for EVERY device.

In other words can the same physical device have data record in more than 1 file of data to be loaded?

If so how do you plan on associating the multiple device records to the same physical device?

Frank Kulash

Hi,

I don't see any reason to have two sequence-generated unique keys in the same table.  If users are content seeing a numeric serial number in the interface, it might as well be the one you already have.

As John said in reply #1, what is the harm in showing the primary key?  If someone thinks that is a bad ideas, ask them to explain.  You might post their answer here; it could be interesting.

The general rule is that a primary key should not do anything except uniquely identify a row.  If someone can tell, just by looking at a device_Id, how old it is, or where it is located, or who manufactured it, or anything else about the device, then it's probably not a good primary key.  For example, a user should not be able to see device_id = 83 and deduce "It must be an old device; it has a 2-digit number" or "It's in the eastern region, since the last digit is 3". 

mathguy

I didn't mean "you" using my SSN in your own database (if you are my dentist, say). Rather, I mean the Social Security Administration itself, and the various users who feed into and from their database.

Same with the bank - my bank assigns an account number to me, perhaps by sequence, starting from their database and then flowing from there to everywhere else.

I guess your suggestion, in these cases, is that the SSA and the bank are doing it wrong. They should assign a SSN to me (or an account number), and that number should be unique, but such numbers have no business being the PK in their tables. I would agree with that 100%, but I don't control what the bank and the government are doing, in their IT department. If for whatever reason they DID use these sensitive numbers as PK (as they may have, I don't know), then I wouldn't want them to expose this PK.

I think we are pretty much in agreement, I am perhaps just more paranoid than most.     On the other hand, if such idiotic practices were not present in real life, I wonder how we get such huge data breaches seemingly all the time...

John Thorton

mathguy wrote:There are cases when you may want to hide a PK, even if the PK is generated by a sequence. See how close your situation is to these examples. (I don't see, in particular, how such things might apply to id's assigned to devices, not to people or to financial assets.)1. Personal identification number/code (in the U.S., Social Security Number). At least in some countries, identity theft is a big concern, and the most valuable piece of information for thieves is the PIN (SSN in the U.S.). One might make the case that SSN's are generated by sequence (with a number of rules though). And some databases do use the SSN as primary key. I would not want that PK to appear in the UI, if the UI is to be accessed by users who don't otherwise have access to the PIN/PIC/SSN.2. Same with bank or brokerage account numbers, mapping to customer id. Right now your users can access information by customer id. They may need to be able to check information from one account (say from the "joint checking account") but they have no business knowing the actual account number. Account numbers, again, are probably generated by sequence. I wouldn't want to expose those numbers to the "general public" (depending on who, exactly, has access to the UI).

The reality that you desire to NOT display certain data columns has NOTHING to do with the happenstance that they are Primary Keys;

but everything to the sensitive nature of the value itself.

You would avoid displaying the columns mentioned above, even if another column was the Primary Key to the table where they reside.

Some folks "insist" that every table have a Primary Key.

Much more often than not, it is 100% innocuous to display Primary Key.

There are certain situations where displaying PK would be unwise, but that has no real connection these values are Primary Keys per se.

Mark D Powell

GregV, a new bank account number is normally constructed rather than manually entered on creation so I think account numbers may well be a PK.  I have seen SSN used as the PK in several systems though more modern practice is probably to use a surrogate key instead.

- -

ALL, contrary to what people believe SSN are not unique since somewhere between 10K and 40K were reissued by mistake and the article below indicates there were some other issues related to uniqueness.  The SSN can contain a non-numeric digit usually appended to the end of the number.  Such numbers are generally assigned to non-citizens who inherit benefits.

- -

Article on SSN

https://www.ssa.gov/policy/docs/ssb/v69n2/v69n2p55.html

- -

user3372439, I think BEDE and MathGuy gave you good responses.

- -

HTH -- Mark D Powell --