Foreign Key Usage

Foreign Key Usage

  

Hi 

When creating entities in OutSystems we always have an Attribute called Id - In order to create a relationship from one entity to another we drag the Id attribute from one entity to another. Therefore the Id from one becomes a Foreign key in the second entity. 


I have an entity which contains an attribute called MemberNo ( Member Number ) which is unique and can be used as a Foreign key 


Doing so would mean deleting the Id attribute in the entity and making the MemberNo attribute the identifier 


Is this good practise in OutSystems ?


Or Should I retain the Id attribute as the Identifier ?


Solution

Hi Omran,

You should retain the Id attribute as the Identifier.


Reagrds

-SK-

Solution

Thanks Shashi, that's exactly what I did 

Well, one of the fundamental rules of DB design is to never use business data as a primary key for a table.

Sure, you've been told the MemberNo is both unique and immutable, but will it still be unique and immutable ten years from now?

The surrogate key on the other hand, because it has no business value not only is it really unique, since it's generated by SQL Server itself and not entered by a user, and immutable, since no change to business data affects it, it can actually be changed some time in the future without affecting any of the business data (provided you propagate the change to all the tables where it's used).

On the other hand, having an ID column does mean you'll need two indexes, and that does mean a slightly higher insert/update cost.