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

Paul Susanto

Yes, I agree with you, apart from the Primary key column which in certain cases can be simply a sequence generated value, I will recommend from my last experience that is is better to have some other unique field as well.

Regards,

Susanto Paul

mathguy

I agree, in the examples I gave the concern was with privacy and confidentiality. I wouldn't want "the general public" to access my medical history, past political affiliations, or even (for some people) the purchasing habits, any more than my social security number or bank account number. But the distinguishing characteristic of the examples I have is that they are (or may be) generated by sequence, and therefore MAY (even thought they SHOULDN'T) be used as PK. In that sense, "not displaying them" has everything to do with the poster's question, which was about including PK in the UI. No one would think of using my medical history or voting record as PK in a table; so that comparison doesn't make sense in this context.

The more relevant question, I believe, is whether there are similar concerns with publishing the PK values generated by sequence for a bunch of devices, not for human beings - and, even if such concerns may exist (I can't think of any, but I am not engaged in industrial espionage either, or in trying to sabotage efforts of foreign governments or companies - or defending such governments or companies from attacks) - so, even if such concerns may exist in general, whether they apply to our OP.

Unknown

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 .

Ok - but what prevents the SAME PHYSICAL DEVICE info from being duplicated in that table?

1. add widget #1 name/model/other - gets sequence number 23

2. add widget #1 again using same name/model/other - gets sequence number 37

Those are the SAME device. What prevents 100 or 1000 of that 'same device' from being entered and getting 100 or 1000 different numbers?

That is the FIRST question/problem that needs to be addressed.

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

You already have a 'unique key for each device' - it can be used to search.

BUT - the NEXT question/problem to be addressed is HOW will a user know what unique key they want to search for? Where does the user get the key value to use for a search?

Unless they already know that 'unique number' for the device then typically they might first search the 'device table' to find the device of interest and then use the 'unique number' for that device to search other tables for data. But again - you already have a unique number for each device (the sequence).

Also - if the user has to search the device table to find the device of interest then the second search can just use the results from the first search and the user NEVER needs to know what that 'unique' device number is.

So how does a user know what device number they want to search for? Is it on a piece of paper somewhere? an invoice? payment? other?

The team has asked me to create a new column and increment using a sequence . 

You already have one.

Can't we use the existing primary key as a unique key?

Yes - a primary key MUST BE UNIQUE - or it wouldn't be a primary key.

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

What does 'showing primary key on UI' have to do with anything? There is NOTHING in what you posted that even discusses what is, or is not, shown on the UI.

You don't have to show a primary key on the UI in order to be able to use a key value to do a search.

The two aren't even related.

You should ONLY show on the UI:

1. info that the current user is entitled to see based on your security requirements

2. info that the current user needs for some business purpose.

Tell us what ALL of the requirements are for a user that wants/needs to do this new 'device search'.

In particular tell us how they are going to know in advance what this new 'unique device number' is no matter HOW you implement it.

Sven W.

It depends.

If the table has a UK aside from the PK, then it is preferable to hide the PK and always show the UK.

The assumption here is, is that the PK is a meaningless technical ID and the UK is some business related value that the user can refer to.

If the table has only a PK, then yes sometimes it makes sense to show that PK to the end user. Altough there is a certain risk that this raises some strange expectations about that ID.

For example I had an application that was essentially an ordering system. The PK for an order was an ID, but there was no real UK. So in the gui the users did see something like "Order Number 12345". This makes sense. However it once happend that the order numbers suddenly jumped by values of 20. Whereus in the past they usually were monoton increasing numbers. And the users complained about that.

Reason had of cause to do with sequence caching (there was an issue with SGA sizing and sequence caches did age out of the SGA too fast). But it shows that even if it is a meaningless technical number, as soon as the users see that value, they start to have expectations about it.

This is something to bear in mind.

AndrewSayer

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

Someone saying something is not good practice with zero justification is not an argument in my book.

You should ask them why.

Mike Kutz

Sven W. wrote:It depends.If the table has a UK aside from the PK, then it is preferable to hide the PK and always show the UK. The assumption here is, is that the PK is a meaningless technical ID and the UK is some business related value that the user can refer to.If the table has only a PK, then yes sometimes it makes sense to show that PK to the end user. Altough there is a certain risk that this raises some strange expectations about that ID. For example I had an application that was essentially an ordering system. The PK for an order was an ID, but there was no real UK. So in the gui the users did see something like "Order Number 12345". This makes sense. However it once happend that the order numbers suddenly jumped by values of 20. Whereus in the past they usually were monoton increasing numbers. And the users complained about that. Reason had of cause to do with sequence caching (there was an issue with SGA sizing and sequence caches did age out of the SGA too fast). But it shows that even if it is a meaningless technical number, as soon as the users see that value, they start to have expectations about it.This is something to bear in mind. 

Worst - The sequences stays monotonic and the end-user uses the expected number instead of the database generated number.  The solution to that problem included usage of DBMS_RANDOM.string().  For data from this table, the PK is hidden to all except the "data manager" pages.

For me, I usually design under the concept of "Under normal conditions, the end-user does not need to see the PK".  For those pages, the PK is hidden by default.  eg The PK column is not part of the Default Report for an APEX IR but it can still be accessed if the end-user chooses to do so.

MK

Badam123

Hi John

I didn't get your question .

But yes , single record will exist for single device and multiple devices can be mapped to single customer.

Badam123

Thanks Frank , I shall check with team and get back

Regarding your example , Could you please elaborate why we shouldn't  have a primarykey which could be seen and understood.

Is there any harm when it is partially explicable (instead of full) ?

Example : CEM12345    

C - California state

E - Eastern region

M - Male

12345 - unique number assigned .

Here in this case the last 5 digits changes for males in the same region . Can we use this as primary key ?

Badam123

Hi Bede

I don't understand how we can lose if entered multiple times

Marwim

A sequential Order ID might be exactly the reason why it should not be displayed. It could give third parties hints about the total sales.

While the german tax office demands a gapless numbering of invoices this is the reason why gapless numbers for each customer are allowed.