I am designated emigrate a database to some mid-class ERP. The brand new system uses composite primary secrets in some places, and from the practical perspective, why?
In comparison to autogenerated IDs, I'm able to only see negative aspects
- Foreign secrets becomes fuzzy
- Harder migration or db-redesigns
- Inflexible as business change. (My vehicle doesn't have reg.plate..)
- Same integrity better accomplished with constraints.
It's falling to the look idea of candiate secrets, that we neither see the purpose of.
Could it be a habit/artifact in the floppy-days (reducing space/indexes), or am I missing something?
//edit// Just found good SO-publish: Composite primary secrets versus unique object ID area //
Composite secrets are needed whenever your primary secrets are non-surrogate and naturally, um, composite, that's, breakable into several non-related parts.
Some real-world good examples:
Many-to-many link tables, where the primary secrets are comprised from the secrets from the organizations related.
Multi-tenant programs when
tenant_idis part of primary key of every entity and also the organizations are just linkable inside the same tenant (restricted with a foreign key).
Programs processing third-party data (with already provided primary secrets)
Observe that realistically, all this is often accomplished utilizing a
UNIQUE constraint (additional to some surrogate
However, you will find some implementation specific things:
Some systems will not let a
FOREIGN KEYmake reference to anything that's not really a
Some systems would only cluster a table on the
PRIMARY KEY, hence making the composite the
PRIMARY KEYwould improve performance from the queries joining around the composite.
Composite primary key provides better performance if this involves them getting used as Foreign secrets in other tables and reduces table reads - sometimes they may be existence savers. If you are using surrogate secrets, you must see that table to obtain natural key information.
For instance (pure example - so we're not speaking DB design here), allows if you have an
ORDER table and
ORDERITEM. If you are using
LineNumber (UPDATE: so that as Pedro pointed out
OrderId as well as
OrderNumber) as composite primary type in
ORDERITEM, then inside your mix table for
SHIPPING, you'd have the ability to have ProductId within the
SHIPPING_ORDERITEM. This could massively improve your performance if for instance you've exhaust that product and want to discover all items of this
ProductId that should be shipped with no have to join.
However, if you are using a surrogate key, you need to join and also you finish track of a really inefficient SQL execution plan where it needs to do bookmark research on several indexes.
See more on bookmark research which using surrogate secrets turns into a major problem.