In my experience, some designers used primary key to enforce logical
parent/child relationship (where the parent's pk is prefixed into one of
the child's primay key), and usually to signify the pk fields to be
"uneditable" since it actually also doubles as "foreignkey", the value
refer to the child's parent's field, though some let it editable for
special case of optimization (imagine it like C pointer, to quickly
switch a child's parent).
Surely group of unique fields can be used as well, but it's less
efficient in defining relationship to its parent if the relationship is
on more than one field. Example (orm/django style):
EmployeeExpenseDetail
- id (pk)
- company id (FK)
- employee id (FK)
- expense id (FK)
- expense detail no
- amount
=> unique(company id, employee id, expense id, expense detail no)
Many "bloat" will come from this design:
- the additional dummy id as pk,
- additional unique index,
- tend to be sql join intensive,
- for the logical aspect, the inability to used the same id for
different company (for employee), or the same id for different
company+employee (for expense id). Ie: Useful to have employee Id
"JOHNDOE" both in company SPAM CORP and FOOD CORP. Thus forcing the use
of dummy id in employee table (and change the FK to points to that dummy
field -- enforcing a join to get employee id).
The dummy id is especially hurting the model, since it's actually
hackish, but at the same time become all-too-important tumor (has to be
maintained with the same respect as other "real" field) that can not be
left out of the table for good.
But I am not sure to what extend this will become annoying, since single
pk also offers excellent simplification in defining how to address a
single row (always a 1 field instead of possibility of multi fields in
multi pk style).
Mike wrote:
Well, if my app can't manage to respect the data integrity, then my app
is junk! I have no objection against data integrity either, redundant
but necessary enough. The point I was making was, what's the difference
between a primary key vs. any other unique field?
