Che cos'è una buona convenzione per il nome dell'ID della chiave della tabella?

Con id rispetto a table_id , sei su sei, una mezza dozzina sull'altro. In entrambi i casi ha lo stesso numero di caratteri in una query (user_id vs user.id). E invece di conflitti su 'id', si hanno potenziali conflitti sulle colonne unite se altre tabelle lo fanno riferimento.

Personalmente, io uso id come ovvio, anche se potrei non aver bisogno di un ID autoincrementante. La mia raccomandazione è di sceglierne uno ed essere coerenti. Quello che non vuoi è alcune tabelle con id e altre con table_id .

Quello che ho visto in quei database che usano surrogati per tutto è il seguente:

    - person_id
    - account_id
    - event_id
    ...etc

Per quanto riguarda la tua affermazione:

...however your software must know the ID name for each table.

Mi viene in mente che stai usando un framework di mappatura OR, nel qual caso dovresti seguire i consigli di quel framework. O forse stai usando uno strumento RAD che creerebbe per te tabelle, moduli e query, nel qual caso dovresti rispettare ciò che ti dà lo strumento.

Se stai modellando tu stesso il database e scrivi tu stesso le query, il suffisso _id è molto comune.

EDIT: PPDM 's Architechtural Principles fornisce anche _no come suffisso alternativo (abbreviazione per numero), dando account_no , observation_no , sequence_no ecc .:

...surrogate key components such as _ID, OBS_NO or SEQ_NO may be added as a component of the Primary key.

Personalmente preferisco usare la "chiave" per la chiave primaria (surrogata) di una tabella. Questo è un po 'eretico quindi di solito mi conformo a qualunque altra gente si aspetti. Il problema dei nomi dei campi in collisione è vero solo se non si qualificano i campi nella query. Penso che sia una cattiva pratica al di fuori delle query ad-hoc. Ad esempio, diciamo che ho due tabelle (normalizzate):

Organization    Person
------------    ------------
key             key
name            firstname
                lastname
                organization

Una query potrebbe essere simile a questa:

select per.key, per.firstname, per.lastname
  from Person per
  join Organization org
    on org.key = per.organization
 where org.name = :org_name

Lo preferisco perché non devo pensare a quale sia il nome della colonna chiave del tavolo (è sempre 'chiave') e, secondo me, la clausola join ti dice esattamente cosa stai facendo. Ovviamente è un'eresia dare un nome alle cose in modo chiaro e conciso in una DB in modo da poter essere crocifisso per fare una cosa del genere. Pertanto non posso raccomandare questo senza alcuna riserva. Se c'è già uno standard, probabilmente dovresti solo seguirlo. In generale, è più facile lavorare con un approccio di denominazione povero ma uniforme rispetto a un patchwork di approcci di denominazione anche quando alcuni sono veramente buoni.

Preferisco anteporre le mie chiavi esterne al nome della tabella (in forma singolare) ed esportarle senza ulteriori prefissi. Questo rende chiaro che ti unisci alla colonna corretta.

SELECT c.name,
       o.date,
       o.total_amount
FROM   orders AS o JOIN customers c
ON     o.customer_id = c.customer_id

Rispetto a:

SELECT c.name,
       o.date,
       o.total_amount
FROM   orders AS o JOIN customers c
ON     o.id = c.id

Potrebbe essere un po 'più difficile se usi le chiavi naturali.