Should we combine Surrogate Key with Natural Key(Unique Index Mean) for readability and easy query selection?

Question

Edit: currently I will keep question opened. to get an very rough answer.

Shortly, Here's a schema

Asked this question because From this topic https://www.sisense.com/blog/when-and-how-to-use-surrogate-keys/

Combining Natural and Surrogate Keys Certain business scenarios might require keeping the natural key intact as a means for users to interact with the database. In these cases …

If a natural key is recommended, use a surrogate key field as the primary key, and a natural key as a foreign key. While users may interact with the natural key, the database can still have surrogate keys outside of the users’ view, with no interruption to user experience.

If a natural key must be used without an additional surrogate key, be sure to combine it with a surrogate key element. In our financial database example, Expense Reports (ER-123) have a natural key is used in conjunction with a surrogate sequential key. This format prevents many of the natural key side effects listed above.

I really found that maybe solve my problem. As am afraid from surrogate key. But XPO and ORM providers support Surrogate key by (100%). unlike Composite Key. Which described in many articles as a bad in new databases design. Surrogate Key vs Natural Key for EF Surrogate vs. natural/business keys

And many articles about that. This is not case here. Am speak about combine or use both surrogate key and also add natural key (as indexes or unique indexes only not PK) Now back to schema above and topic above that speak about combine both. I have a Branch which have surrogate key Oid, all tables have Oid as surrogate key. Also I have natural key for example (BranchID),

1. Branch Table have (Oid[surrogate], BranchID[Natural Key - Unique Index not PK]
2. InvTransHed have (Oid[Surrogate], itd_brn, itd_type, ith_num) as Natural Key (Unique Index I mean)
3. InvTransDet have (Oid[Surrogate], itd_brm, itd_typ, itd_num, itd_lne as Natural Key [Unique Indexs])

What I actually make is linking all Surrogate keys Oid with its Foreign Key side. for example: Branch.Oid Linked with InvTransHed.BranchSKey [Skey = surrogate key]

Why I need to combine Surrgoate Key with Natural Key(as Unique Indexes)?

1. Easy create reporting with JOINs (Branch with InvTransDet directly without moving to InvTransHed).
2. Readability for technical support. easy to make any join without care about surrogate key. or Linking to parent tables till reach what we need.
3. Easy to understand and ORM Providers (friendly by 100% for sure)

Here's a questions that blown my head:

1. Should I linking Surrogate Keys only with their another side Foreign Key. Or I must link also Natural Indexes to their Natural Keys. Branch.BranchID => InvTransHed.ith_brn?
2. About naming opposite side FK. Branch.Oid surrogate linking with InvTransHed.BranchSkey. Is naming important here to be same for readability?
3. Guide me please for that (Title = Question).

Answer 1

Constraints like PKs and FKs are our primary mechanisms for documenting and enforcing the referential integrity of the database.

We can't rely on documentation and data schema naming conventions alone to explain to developers and users how to interpret the data. The whole point around using an RDBM is to maintain referential integrity. Referential and any check constraints form the basis of "truth" about the rules that must be adhered to when inserting data into the database.

You have rightly pointed out that you want to enforce uniquness of a combination of an FK and a Natural Surrogate key, this is therfore primarily a constraint.

If the existence of the Constraint is not in question then we only need to consider the Index.

• Unique Constraint vs Unique Index

  It is not necessary for a Unique Constraint to also be paired with a Unique Index however for discovery by developers it is simply more documentation or at least more discoverable if you also define it as an Index.

When or If performance becomes an issue, you might change the uniqueness of the index or remove it entirely, however you would always want to maintain the unique constraint in this scenario to enfore the integrity of the data.

---

While not necessary, a consistent naming convention helps with the readability of the data schema and to explain the intent of your structure, consistency is the key though, the first time you violate your convention the reader must now doubt or second guess all the previous assumptions.

For this reason, if conventions might be questioned, existence of constraints will always provide a definitive answer.

The actual naming convention you use becomes personal preference of the designer. I use Id as the PK and {tablename}Id as the FK. When the link is ambiguous, like when a Table has FKs to itself, or multiple FKs to the same table, then I prefix the column with the noun or verb that describes the relationship: CreatedBy_UserId vs ModifiedBy_UserId

I feel that this convention lends itself to readable and natural SQL and C# syntax.

SELECT ID, Description, Modified, ModifiedBy.Name
FROM Product
INNER JOIN User [ModifiedBy] ON Product.ModifiedBy_UserId = ModifiedBy.Id

As a convention, I know that the PK is always Id, So where ever it is used you can assume that it is the principal end of the relationship.

Answer 2

You should never be oblige to have a surrogate key and a primary key, except when the natural key is required. Because the natural key is subject to :

1. a higher length rather than a technical PK (INT IDENTITY as an example)
2. some updates because of user entry errors
3. a dispersion of values that is erratic
4. and the most terrific : datatype can change in the life of the database (recently Europe standardized the vehicle registration so every european countries has to change the datatype for it !)

The longer a key, the poorer the performance and costly maintenance.

Updating a PK is a nightmare especially when there is many Foreign Keys relying them!

When the values of a key as an erratic dispersion, the stats collected are less acurate so it will potentially results less good execution plans

Changing the datatype of a natural key will block many tables for a long time...

But don't forget that you can have Foreign Keys relying on Unique keys. A FK constraint is a contraint based on another UNIQUE or PK constraint...