El mejor método para generar IDs en tablas de movimiento

[darkamerico]

Saludos amigos,
En un entorno multiusuario, garantizar que las IDs (PKs) son únicas es parte fundamental de cualquier sistema bien estructurado.
Actualmente, por ejemplo yo hago lo siguiente:

Código Delphi [-]
for i := 1 to StrToInt(txtNroPagosTDR.Text) do
              begin
                idPago:=clsFx.CalcIDNewPago;
                try
                  dm.q_INSPagoIni.Close;
                  dm.q_INSPagoIni.Params[0].AsLargeInt:=idPago;
                  dm.q_INSPagoIni.Params[1].AsLargeInt:=IdObjeto;
                  dm.q_INSPagoIni.Params[2].AsInteger:=i;   // NroPago
                  dm.q_INSPagoIni.Params[3].AsString:='0';  // Estado Pendiente
                  dm.q_INSPagoIni.Params[4].AsSmallInt:=1;  // Tipo Cronograma Principal
                  dm.q_INSPagoIni.Params[5].AsFloat:=montoCuota;
                  dm.q_INSPagoIni.ExecSQL;
                  savedOk:=True;
                except
                  savedOk:=False;
                end;
                if not savedOk then
                begin
                  ShowMessage('Error: No se pudo Insertar el Registro de Pago - Cuota Simple');
                  exit;
                end;
              end;

Función:

Código Delphi [-]
function TFunciones.CalcIDNewPago: LongInt;
begin
  dm.q_CalcIDNewPago.Close;
  dm.q_CalcIDNewPago.Open;
  result:=dm.q_CalcIDNewPago.Fields[0].AsLargeInt;
end;

Consulta q_CalcIDNewPago:

Código SQL [-]
if((select top 1 id_pago from gestion.pago) is null) select 1;
else select max(id_pago)+1 from gestion.pago

sin embargo, no estoy seguro que sea la mejor forma de hacerlo.

Cualquier aporte será bienvenido.

Atte,
Américo

[mamcx]

Estas duplicando, de forma mas ineficiente*, lo que hace una bd. Usa:

• Llave natural si importa que sea único desde el punto de vista de la logica de negocios
• Autoinc si solo importa que sea único desde el punto de vista de la BD
• UUID si solo importa que sea único desde el punto de vista global

Lo que es dificil es garantizar que sea perfectamente incremental sin 'huecos' pero eso es otro asunto.

* p.d: Y adicionalmente este codigo trae un bug incorporado!

[marco3k]

Creo que la mejor forma de generar un id es con campo autonumerico o autoincrementable. Ahí simplemente el motor de bd genera el siguiente número sin necesidad de averiguar cual es el mayor número de un campo de tabla.

[darkamerico]

Empezado por mamcx

Estas duplicando, de forma mas ineficiente*, lo que hace una bd. Usa:

• Llave natural si importa que sea único desde el punto de vista de la logica de negocios
• Autoinc si solo importa que sea único desde el punto de vista de la BD
• UUID si solo importa que sea único desde el punto de vista global

Lo que es dificil es garantizar que sea perfectamente incremental sin 'huecos' pero eso es otro asunto.

* p.d: Y adicionalmente este codigo trae un bug incorporado!

Te agradecería que me indiques el bug

[[duilioisola]]

En firebird existen los generadores.
Los generadores siempre devuelven números diferentes (garantizado por la base de datos).
Los generadores siempre se incrementan, a pesar de que la transacción falle (Rollback).

Yo suelo dejar que la base de datos lo genere y me lo devuelva.
Con esta sentencia y el generador en el trigger:

Código SQL [-]
CREATE TRIGGER CABECERA_BI0 ...
AS
BEGIN
   new.id = GEN_ID(CONTADOR_CABECERA, 1);
   ...
END

INSERT INTO CABECERA (CLIENTE, FECHA, ...)
VALUES (:CLIENTE, :FECHA, ...)
RETURNING ID

Cuando por alguna razón no puedo utilizarlo de esta manera, pido un número al generador y luego lo utilizo en el insert

Código Delphi [-]
function TDMMain.ContadorGen(NomGen: string): integer;
begin
  Result := 0;
  if (Trim(NomGen) > '') then
  begin
     with THYFIBQuery.Create(nil) do
     begin
        try
           Close;
           DataBase := Self.DataBase;
           SQL.Text := 'SELECT GEN_ID(' + NomGen + ', 1) FROM RDB$DATABASE';
           ExecQuery;
           Result := Fields[0].AsInteger;
           Close;
           FreeHandle;
        finally
           Free;
        end;
     end;
  end;
end;


// Luego inserto llamando al procedimiento para el valor del parámetro ID
INSERT INTO CABECERA (ID, CLIENTE, FECHA, ...)
VALUES (:ID, :CLIENTE, :FECHA, ...)

[[duilioisola]]

En tu caso, básicamente haces un SELECT MAX(ID).

Si justo al mismo tiempo dos usuarios piden el MAX(ID) les devolverá el mismo número.
Luego al hacer el INSERT el primero logrará hacerlo y el segundo dará error porque estará insertando un duplicado.

Particularmente esta forma de obtener un ID es muy ineficiente en Firebird.
A pesar de tener índices por ese campo.
Tiene que ver con la multi versión de registros.
En otros motores no sabría decirte.

[mamcx]

Otro bug: Insertas 10 registros, el id va en 10. Ahora:


Borras el ultimo
Borras todos
Borras el primero

etc.


Un id debe ser global(en su contexto), único y *no repetible*

[darkamerico]

Empezado por mamcx

Otro bug: Insertas 10 registros, el id va en 10. Ahora:


Borras el ultimo
Borras todos
Borras el primero

etc.


Un id debe ser global(en su contexto), único y *no repetible*

Muy interesante, yo uso SQL Server, alguien podría ayudarme con una función en Delphi que genere este ID unico e irrepetible?, no quisiera usar el identity.

Como siempre muchas gracias a todos.

Atte,
Americo Torres

[mamcx]

Negarse a usar lo que el motor te da 'gratis' raramente tiene sentido. No solo es esfuerzo innecesario, es como introduces errores inesperados y limitas al motor a la hora de hacer optimizaciones.

P.D: Yo trabajo en parte construyendo un motor de base de datos desde cero(https://spacetimedb.com) y te puedo asegurar que cada componente se hace con mas detalle de lo que te puedes imaginar. Ahora estoy solamente generando el diseño básico de la autenticación y llevo 3 semanas, como 10 borradores y todo porque en un motor de base de datos hay que tener en cuenta un montón de casos extremos.

Usar el soporte de autonumericos del motor es lo que se debe de hacer a menos que tengas una buena razón, que SOLAMENTE puede ser:


* Necesitas un id universal, así que usas una UUID
* Necesitas evitar `saltos` para el caso de facturas, que entonces es complicado de hacer correcto, porque no solo debes hacer la lógica correctamente sino que debes coordinar a todas tus app que usen un modo transaccional mas estricto `SERIALIZABLE` y eso que no estoy seguro como seria en sql server
* Tienes una llave natural