Re: integer id columns for all tables

garhone schrieb:
Hi,

Someone with greater expertise than I recently told me that it is best
to have an integer primary-key for all tables in a database, even if
the table already has some non-integer primary key, or some sort of
composite primary key. If there is already a non-integer key, then
create a new sequence column and set that as the primary key.

Does anyone have an opinion on this and why?

Such an approach is very good for many applications and it does make sense to use it. Its main advantage is that it more or less successfully separates identification means (primary key in RM) from what is going to be identified.

For complex models it is frequently more convenient to have adequate more complex (non-primitive) identification means. Unfortunately, in most cases using such complex identifiers has more harm than benefits. And, again, the main reason for that is almost complete absence of means for separating two concerns:

- object semantic characterisation (entity modeling) and
- object identification means (identity modeling).

--
http://conceptoriented.com
.

Relevant Pages

  • Re: integer id columns for all tables
    ... to have an integer primary-key for all tables in a database, ... composite primary key. ... The design criteria for candidate keys are: simplicity, stability, familiarity and irreducibility. ... I highly recommend Fabian Pascal's _Practical Issues in Database Management_ book. ...
    (comp.databases.theory)
  • Re: integer id columns for all tables
    ... to have an integer primary-key for all tables in a database, ... composite primary key. ... identifying attribute is now a natural property of the item concerned ...
    (comp.databases.theory)
  • Re: Id aus einer anderen Tabelle
    ... und wenn dann ein Insert gemacht wird, dann bekommt der Datensatz zuerst den Primary-Key ... und durch den Trigger ... dass ein Primary Key unveränderlich ist. ...
    (microsoft.public.de.sqlserver)
  • integer id columns for all tables
    ... to have an integer primary-key for all tables in a database, ... composite primary key. ...
    (comp.databases.theory)
  • Re: integer id columns for all tables
    ... Bob Badour wrote: ... separates identification means (primary key in RM) from what is going to ...
    (comp.databases.theory)