stuff string_agg reconocido recognized not nombre name integrada hana group_concat functions función concatenate built sql database database-design backup

sql - string_agg - ¿Cuál es la mejor manera de implementar la eliminación suave?



string_agg sql server 2008 (14)

Trabajando en un proyecto en este momento y tenemos que implementar eliminación suave para la mayoría de los usuarios (roles de usuario). Decidimos agregar un campo "is_deleted = ''0''" en cada tabla de la base de datos y establecerlo en ''1'' si determinadas funciones de usuario presionan un botón Eliminar en un registro específico.

Para mantenimiento futuro ahora, cada consulta SELECT deberá asegurarse de que no incluyan registros donde is_deleted = ''1''.

¿Hay una mejor solución para implementar la eliminación suave?

Actualización: también debo tener en cuenta que tenemos una base de datos de auditoría que realiza un seguimiento de los cambios (campo, valor antiguo, nuevo valor, tiempo, usuario, ip) a todas las tablas / campos dentro de la base de datos de la aplicación.

Algo que utilizo en proyectos es un statusInd tinyint not null default 0 column using statusInd como una máscara de bits me permite realizar la gestión de datos (eliminar, archivar, replicar, restaurar, etc.). Usando esto en vistas, puedo hacer la distribución de datos, publicación, etc. para las aplicaciones consumidoras. Si el rendimiento es una preocupación con respecto a las vistas, use pequeñas tablas de hechos para respaldar esta información, omitiendo el hecho, descarta la relación y permite eliminaciones escaladas.

Escala bien y está centrado en los datos, manteniendo la huella de datos bastante pequeña: clave para 350gb + dbs con preocupaciones en tiempo real. El uso de alternativas, tablas, desencadenadores tiene algunos gastos generales que, según la necesidad, pueden o no funcionar para usted.

Las auditorías relacionadas con SOX pueden requerir más de un campo para ayudar en su caso, pero esto puede ayudar. Disfrutar

Cuidado con los registros borrados por software que causan violaciones de restricción de exclusividad. Si su base de datos tiene columnas con restricciones únicas, tenga cuidado de que los registros eliminados temporalmente anteriores no le impidan recrear el registro.

Piensa en el ciclo:

  1. crear usuario (login = JOE)
  2. soft-delete (establece la columna eliminada en no nulo)
  3. (re) crear usuario (login = JOE). ERROR. LOGIN = JOE ya está tomado

Segundo crear resultados en una violación de restricción porque login = JOE ya está en la fila de borrado suave.

Algunas técnicas: 1. Mueva el registro eliminado a una nueva tabla. 2. Establezca la restricción de exclusividad en la columna de inicio de sesión y en la marca de fecha y hora deleted_at

Mi propia opinión es +1 para pasar a la nueva tabla. Se requiere mucha disciplina para mantener el * Y delete_at = NULL * en todas tus consultas (para todos tus desarrolladores)

Definitivamente tendrá un mejor rendimiento si mueve sus datos eliminados a otra tabla como Jim dijo, además de tener un registro de cuándo se eliminó, por qué y por quién.

Agregar where deleted =0 a todas sus consultas ralentizará significativamente, y dificultará el uso de cualquiera de los índices que pueda tener sobre la mesa. Evite tener "banderas" en sus tablas siempre que sea posible.

Eso depende de la información que necesita y de los flujos de trabajo que desea admitir.

¿Quieres ser capaz de:

  • saber qué información estaba allí (antes de ser eliminada)?
  • saber cuando fue eliminado?
  • ¿Sabes quién lo borró?
  • saber en qué capacidad estaban actuando cuando lo eliminaron?
  • ser capaz de eliminar el registro?
  • ser capaz de decir cuándo fue eliminado?
  • etc.

Si el registro se eliminó y eliminó cuatro veces, ¿es suficiente que sepa que actualmente se encuentra en un estado no eliminado, o desea poder contar lo que sucedió mientras tanto (incluidas las ediciones entre sucesivas)? eliminaciones!)?

La mejor respuesta, lamentablemente, depende de lo que está intentando lograr con sus eliminaciones suaves y la base de datos en la que está implementando esto.

En SQL Server, la mejor solución sería usar una columna deleted_on / deleted_at con un tipo de SMALLDATETIME o DATETIME (dependiendo de la granularidad necesaria) y hacer que esa columna sea nulable. En SQL Server, los datos del encabezado de fila contienen una máscara de bits NULL para cada una de las columnas de la tabla, por lo que es marginalmente más rápido realizar IS NULL o IS NOT NULL que verificar el valor almacenado en una columna.

Si tiene un gran volumen de datos, querrá analizar sus datos, ya sea a través de la base de datos o a través de dos tablas separadas (por ejemplo, Productos y ProductHistory) oa través de una vista indizada.

Normalmente evito los campos de banderas como is_deleted, is_archive, etc. porque solo tienen un significado. Un campo delete_at, archived_at con nulidad brinda un nivel adicional de significado para usted y para quien hereda su aplicación. Y evito los campos de máscara de bits como la peste, ya que requieren una comprensión de cómo se construyó la máscara de bits para comprender cualquier significado.

Me inclinaría hacia el "método Rails" con una columna deleted_at que contiene la fecha y hora de cuando se realizó la eliminación. Luego obtienes un poco de metadatos gratis sobre la eliminación. Para su SELECT solo obtenga filas WHERE deleted_at IS NULL

Prefiero mantener una columna de estado, así que puedo usarla para varias configuraciones diferentes, es decir, publicadas, privadas, eliminadas, needsAproval ...

Tener una columna is_deleted es un enfoque razonablemente bueno. Si está en Oracle, para aumentar aún más el rendimiento, recomiendo dividir la tabla creando una partición de lista en la columna is_deleted. Luego, las filas borradas y no eliminadas estarán físicamente en diferentes particiones, aunque para usted será transparente.

Como resultado, si escribe una consulta como

SELECT * from table_name where is_deleted = 1

entonces Oracle realizará la ''eliminación de particiones'' y solo buscará en la partición apropiada. Internamente, una partición es una tabla diferente, pero es transparente para usted como usuario: podrá seleccionar en toda la tabla sin importar si está particionada o no. Pero Oracle podrá consultar SOLAMENTE la experiencia que necesita. Por ejemplo, supongamos que tiene 1000 filas con IS_DELETED = 0 y 100000 filas con IS_DELETED = 1, y usted divide la tabla en IS_DELETED. Ahora si incluye condición

WHERE ... AND IS_DELETED=0

entonces Oracle SÓLO explorará la partición con 1000 filas. Si la tabla no estuviera particionada, debería escanear 101000 filas (ambas particiones).

no mencionas qué producto, pero SQL Server 2008 y postgresql (y otros, estoy seguro) te permiten crear índices filtrados, para que puedas crear un índice de cobertura donde is_deleted = 0, mitigando algunos de los aspectos negativos de este enfoque particular .

si la tabla es grande y el rendimiento es un problema, siempre puede mover los registros ''eliminados'' a otra tabla, que tiene información adicional como la hora de eliminación, quién borró el registro, etc.

de esa manera no tiene que agregar otra columna a su tabla principal

utilice una vista, función o procedimiento que compruebe is_deleted = 0, es decir, no seleccione directamente en la tabla en caso de que la tabla deba cambiar más tarde por otros motivos

e indexe la columna is_deleted para tablas más grandes

como ya tiene un seguimiento de auditoría, el seguimiento de la fecha de eliminación es redundante

Puede realizar todas sus consultas en una vista que contenga la WHERE IS_DELETED=''0'' .