Use case for custom primary key

Dear folks,

May I know of any situations whereby it becomes necessary to do away with the default behaviour of outsys using a sequential no. as primary key and having to use an actual no. e.g. 'contract id' to be the PK instead?

My thinking is that we can always set attribute 'contract id' to be unique through indexing thus ensuring a 1 to 1 mapping between the default outsys key and the actual biz key of 'contract id'.

Appreciate any light on this.

Thanks!

Tuck

Hi Tuck,

There can be several use cases:

  • For static entities I would advice always to switch off auto number, to ensure consistency accross enviroments. Assign your own integer IDs or maybe even text ID. This ensures that over all environments the save key is used for a static entity record. This makes it easier top import/export data between databases without breaking references to static entities.

  • If you need id's that are not 'tracable' like a GUID. If you have a detail screen with an integer ID, then a user could manually update the url to the next ID and maybe get access to data that should not be accesible. Using a GUID this is no long possible as the GUID has no implied sequence.

Regards,

Daniel

In additions to Daniël's answer, the #1 reason for using a foreign key as identifier, is for optional extension Entities. Say you have a primary Entity holding some basic data, and depending on some type you have either one set of extra data, or another one. You can then have two extension Entities, for each set of data, and since it's a 1:1 relationship with the primary Entity, you can use the primary Entity's identifier as identifier for the extension Entities. Another reason would be if you want to extend an Entity you cannot change, e.g. system Entities like User.

Why you wouldn't want an autonumber + "normal" foreign key reference + unique index is that a) it isn't clear from the data model it's a 1:1 instead of a 1:n relationship, and b) it's slower (you have an extra index to maintain, that also must check for uniqueness).

Kilian Hekhuis wrote:

In additions to Daniël's answer, the #1 reason for using a foreign key as identifier, is for optional extension Entities. ...

Of course, that would be the most obivous reason, how could I forget to mention that.

Regarding the first example of Daniel, it is indeed a best practice to change the id of Static Entities.

On the second I tend to disagree. Sometimes you can have the integer id for key and use it internally, but use a guid (not id) as the parameter in URL for the users.

I would add the case of having some stable and reliable ID that you could reuse while importing data from multiple tables. Example: historical data keeping the original id so it is consistent across secondary tables like it used to be.