Forum Stats

  • 3,852,465 Users
  • 2,264,106 Discussions
  • 7,905,072 Comments

Discussions

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

13»

Answers

  • Sven W.
    Sven W. Member Posts: 10,551 Gold Crown
    edited Mar 14, 2018 7:45AM

    Please note that I never mentioned a requirement like gapless numbers. If this is an requirement then a sequence is not the right solution as we all know.

    In my case the users never had a problem when the order_id was jumping from time to time. However they expressed concerns when the order number jumped by 20 values each and every time. And they were right to be concerned about it, since there was a technical issue behind that.

    My point was that as soon as we start showing ID values (filled from a seqeunce) to end users, they start working with that number. This is not wrong - but it is something to be aware of and to consider upfront. The ID then starts to replace the business UK.

    Also hiding ID values to avoid giving third parties information is not a valid security solution. "Security by obscurity"  is not something to aim for (https://en.wikipedia.org/wiki/Security_through_obscurity ):

    The National Institute of Standards and Technology (NIST) in the United States specifically recommends against this practice: "System security should not depend on the secrecy of the implementation or its components."
    [6]
  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Mar 14, 2018 8:01AM
    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

    I agree with the team. Do not expose primary key (row ID) and let it seem to be business key as a part of business data.

    Surrogate primary key has no other domain than keep row unique ID. That has no relation to business. Device must be unique based on its attributes (business key), not by its row ID.

    Surrogate keys may change and like rp024 points out, there are possibility to have many rows of same device (by business key) but with totally different PK aka row ID.

    Don't give a column multiple meanings is my argument.

    L. Fernigrini
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,488 Red Diamond
    edited Mar 14, 2018 10:01AM

    Hi,

    3372439 wrote:Thanks Frank , I shall check with team and get backRegarding your example , Could you please elaborate why we shouldn't have a primarykey which could be seen and understood.

    I know that's unintuitive.  I had a hard time believing that dictum when I first heard it.  But maintenance is eaisery, and complications are reduced, if the primary key doesn't contain any information (other than uniquely identifying the row).  Here's why:

    If you need to update the target of a foreign key, then you need to update all the children in other tables that reference that foreign key.  That's a lot of unnecessary work.

    If a column contains any real information, you may need to update it.  For example, if you have a key that identifies location, the location may change, or you may discover the location was entered incorrectly.  So targets of foreign keys should be surrogate keys, that contain no real information.

    Primary keys are often targets of foreign keys.  There may not be any foreign keys when you create the table, but applications grow.  When you create something today, you want to design it in a way that any maintenance you may need to do next year (such as adding a foreign key) will be as simple and robust as possible.

    Is there any harm when it is partially explicable (instead of full) ?Example : CEM12345 C - California stateE - Eastern regionM - Male12345 - 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 ?

    The same reasons apply if any part of the key contains real information.

    The example above has another problem: it has four independent pieces of information in one column.  Relational databases work best when each column of each row contains (at most) one piece of information.  This rule is so basic to database design that it's called First Normal Form.   A better design would have four separate columns (state, region, gender and unique_key).  I will make queries simpler and more efficient when you need to do things like include only certain regions.

    If you need to display all the information together in a single string, like 'CEM12345', it's easy to concatenate the values together.

    Columns are like atoms.  It's relatively easy to combine atoms into molecules. While it is possible to split atoms apart, it's much harder.

    Badam123Badam123
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    edited Mar 14, 2018 12:26PM

    I only took the ORDER_ID as an example for data that might tell more than you intend. I didn't say that it should be generated by a sequence.

    Also hiding ID values to avoid giving third parties information is not a valid security solution. "Security by obscurity"  is not something to aim for (https://en.wikipedia.org/wiki/Security_through_obscurity ): The National Institute of Standards and Technology (NIST) in the United States specifically recommends against this practice: "System security should not depend on the secrecy of the implementation or its components."
    [6]

    If I have to choose between a requirement from German tax office and a NIST recommendation I'm sure what I'll take :-)

    Edit: Of course a gapless invoice number should not be the primary key.

    The primary key should be generated by a sequence, the invoice number later when the order is confirmed.

    Edit 2: And it is not about System Security but business secrets

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Mar 14, 2018 12:17PM
    The National Institute of Standards and Technology (NIST) in the United States specifically recommends against this practice: "System security should not depend on the secrecy of the implementation or its components."
    [6]

    The gap between "should not" and "does not" is so big, Arnold Schwarzenegger could drive his Hummer through it.

    The issue does not arise (perhaps) in systems that comply with the NIST recommendation. How many systems in real life DON'T comply with it?

    More relevant to this thread: How much risk can a new developer joining an existing organization take in this regard? Can they assume that the system they inherited was built in compliance with the NIST recommendation?

  • Unknown
    edited Mar 14, 2018 12:40PM
    Could you please elaborate why we shouldn't  have a primarykey which could be seen and understood.

    I already 'elaborated' extensively on why what can be SEEN has NOTHING to do with whether the value is a primary key or not.

    It is ALL about SECURITY and whether the user needs (and is allowed) to see the info to do their job.

    If a user either needs to see a data value, or you simply want to allow them to see a data value then put it in the UI and let them see it.

    Not sure what is so hard to understand about that.

    Is there a SPECIFIC question you have about the above guidelines?

  • BEDE
    BEDE Oracle Developer Member Posts: 2,456 Gold Trophy
    edited Mar 15, 2018 3:41AM

    That is that you actually may have the same item entered N times and, looking at what is in the table have more items than in real life... So, you "lose it" figuratively speaking because looking at what is in the table you don't have the correct image of what is in real life.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,056 Blue Diamond
    edited Mar 15, 2018 5:25AM

    3372439,

    I won't make any comment about the gaps in the model you've described, but I'll offer a hypothesis regarding your company rule on primary keys and UIs.

    Sometimes the answer to "Why do you do X?" is "Because 15 years ago we tried doing Y and something broke so we did X as a workaround and now we always do X".

    A possible argument for "don't display the primary key on UI screens" is that a common (historic) mechanism for handling updates from UI screens was to generate a single SQL statements that said: "update the row in the database with every column that's on the display screen - whether or not it has changed - based on the rowid". If your screen display includes the primary key then you've just updated the primary key (even if it hasn't changed) so you are at risk of the "foreign key locking" issue unless you've created indexes on every foreign key that relates to that primary key column. Since it can be a huge waste of resources to create massive indexes for cases where the optimizer would never use them it would have made sense to find a way around this UI issue - and an easy option is to have a rule that says "don't display the primary key".

    Once enough time has passed there's no-one around that knows why something is done, only that it is done and that it might be dangerous not to do it.

    Regards

    Jonathan Lewis

    EtbinMarwimBEDEBadam123
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Mar 15, 2018 1:15PM

    Jonathan, >> Once enough time has passed there's no-one around that knows why something is done, only that it is done and that it might be dangerous not to do it.  <<

    - -

    so true. 

    - -

    user3372439, you have gotten a lot of reasonable responses so I would think you should probably mark this thread as answered using the ACTION link in the upper right of the screen when you are in the thread (rather than in the mail box).

    - -

    IMHO -- Mark D Powell --

This discussion has been closed.