sql server - venta - Diseño de base de datos para facturas, líneas de factura y revisiones

sistema facturacion base de datos

Question

Estoy diseñando la segunda iteración principal de una base de datos relacional para el CRM de una franquicia (con mucha refactorización) y necesito ayuda sobre las mejores prácticas de diseño de bases de datos para almacenar facturas de trabajo y líneas de facturación con un sólido registro de auditoría de cualquier cambio realizado en cada una factura.

Esquema actual

Tabla de Invoices

InvoiceId (int) // Primary key
JobId (int)
StatusId (tinyint) // Pending, Paid or Deleted
UserId (int) // auditing user
Reference (nvarchar(256)) // unique natural string key with invoice number
Date (datetime)
Comments (nvarchar(MAX))

Tabla de InvoiceLines

LineId (int) // Primary key
InvoiceId (int) // related to Invoices above
Quantity (decimal(9,4))
Title (nvarchar(512))
Comment (nvarchar(512))
UnitPrice (smallmoney)

Esquema de revisión

Tabla de InvoiceRevisions

RevisionId (int) // Primary key
InvoiceId (int)
JobId (int)
StatusId (tinyint) // Pending, Paid or Deleted
UserId (int) // auditing user
Reference (nvarchar(256)) // unique natural string key with invoice number
Date (datetime)
Total (smallmoney)

Consideraciones de diseño del esquema

1. ¿Es sensato almacenar el estado Pagado o Pendiente de una factura?

Todos los pagos recibidos por una factura se almacenan en una tabla de Payments (por ejemplo, efectivo, tarjeta de crédito, cheque, depósito bancario). ¿Es significativo almacenar un estado "Pagado" en la tabla Invoices si todos los ingresos relacionados con las facturas de un trabajo dado se pueden deducir de la tabla de Payments ?

2. ¿Cómo realizar un seguimiento de las revisiones de línea de pedido de factura?

Puedo realizar un seguimiento de las revisiones de una factura almacenando cambios de estado junto con el total de la factura y el usuario de auditoría en una tabla de revisión de facturas (consulte InvoiceRevisions ), pero es difícil mantener el seguimiento de una tabla de revisión de línea de factura. ¿Pensamientos? Editar: las líneas de pedido deben ser inmutables. Esto se aplica a una factura "en borrador".

3. Impuesto

¿Cómo debo incorporar el impuesto a las ventas (o el 14% de IVA en SA) al almacenar los datos de la factura?

Editar: buenos comentarios, chicos. Las facturas y las líneas de facturación son, por definición , inmutables , por lo que el seguimiento de los cambios no es razonable. Sin embargo, una factura "en borrador" debe ser editable por más de una persona (p. Ej., El administrador aplica el descuento después de que el técnico crea la factura) antes de que se emita ...

4. ¿La mejor manera de definir y rastrear el estado de la factura?

1. Borrador
2. Emitido
3. Anulado

... limitado a cambiar en una dirección?

Answer 1

¿Por qué no simplemente crear copias de las tablas que desea auditar y que en las tablas originales crear triggins que copiará una fila a las copias de la tabla en cada inserción, actualización, eliminación?

El desencadenador generalmente se ve así:

CREATE TRIGGER Trg_MyTrigger
   ON  MyTable
   AFTER UPDATE,DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [DB].[dbo].[MyTable_Audit]
           (Field1, Field2)
     SELECT Field1, Field2
    FROM DELETED
END
GO

Answer 2

Estoy de acuerdo con el comentario anterior de Aaronaught con respecto a la "inmutabilidad" de la factura.

Si sigue ese consejo, consideraría tener "Pendiente de revisión", "Aprobado" y "Anulado" como estados. "Revisión Pendiente" es solo eso. "Aprobado" se considera correcto y pagadero por el cliente. "Anulado" es solo eso: la factura ya no es válida y no es pagadera por el cliente. Luego puede deducir si la factura se paga en su totalidad desde los registros en Payments , y no está repitiendo información.

Aparte de eso, sin embargo, no hay problemas reales con su idea de revisión.

Puede incluir impuestos como solo otro registro en InvoiceLines .

Answer 3

Mi consejo de aproximadamente 4 años de tener que trabajar con el back-end de un sistema de facturación que alguien más diseñó: no tiene un estado "pendiente" en las facturas. Te volverá loco.

El problema con el almacenamiento de facturas pendientes como facturas ordinarias (con un indicador / estado "pendiente") es que habrá cientos de operaciones / informes que solo deben tener en cuenta las facturas contabilizadas , lo que significa literalmente cada estado excepto pendiente. Lo que significa que este estado debe verificarse cada. soltero. hora. Y alguien va a olvidar. Y pasarán semanas antes de que alguien se dé cuenta.

Puede crear una vista ActiveInvoices con el filtro pendiente incorporado, pero eso simplemente cambia el problema; alguien olvidará usar la vista en lugar de la mesa.

Una factura pendiente no es una factura. Se indica correctamente en los comentarios de la pregunta como un borrador (o una orden, solicitud, etc., todo el mismo concepto). La necesidad de poder modificar estos borradores es comprensible, definitivamente. Así que aquí está mi recomendación.

Primero, crea una tabla de borrador (la llamaremos Orders ):

CREATE TABLE Orders
(
    OrderID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED,
    OrderDate datetime NOT NULL
        CONSTRAINT DF_Orders_OrderDate DEFAULT GETDATE(),
    OrderStatus tinyint NOT NULL,  -- 0 = Active, 1 = Canceled, 2 = Invoiced
    ...
)

CREATE TABLE OrderDetails
(
    -- Optional, if individual details need to be referenced
    OrderDetailID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_OrderDetails PRIMARY KEY CLUSTERED,
    OrderID int NOT NULL
        CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY
            REFERENCES Orders (OrderID)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    ...
)

CREATE INDEX IX_OrderDetails
ON OrderDetails (OrderID)
INCLUDE (...)

Estas son sus tablas básicas de "borrador". Ellos pueden ser cambiados. Para rastrear los cambios, debe crear tablas de historial, que tengan todas las columnas que están en las tablas originales Orders y OrderDetails , más columnas de auditoría para el último usuario modificado, fecha y tipo de modificación (insertar, actualizar o eliminar).

Como menciona Cade, puede usar AutoAudit para automatizar la mayor parte de este proceso.

Lo que también deseará es un activador para evitar actualizaciones de borradores que ya no están activos (especialmente los borradores que se publican y se han convertido en facturas). Es importante mantener esta información consistente:

CREATE TRIGGER tr_Orders_ActiveUpdatesOnly
ON Orders
FOR UPDATE, DELETE
AS

IF EXISTS
(
    SELECT 1
    FROM deleted
    WHERE OrderStatus <> 0
)
BEGIN
    RAISERROR(''Cannot modify a posted/canceled order.'', 16, 1)
    ROLLBACK
END

Como las facturas son una jerarquía de dos niveles, necesita un disparador similar y un poco más complicado para los detalles:

CREATE TRIGGER tr_OrderDetails_ActiveUpdatesOnly
ON OrderDetails
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS
(
    SELECT 1
    FROM
    (
        SELECT OrderID FROM deleted
        UNION ALL
        SELECT OrderID FROM inserted
    ) d
    INNER JOIN Orders o
        ON o.OrderID = d.OrderID
    WHERE o.OrderStatus <> 0
)
BEGIN
    RAISERROR(''Cannot change details for a posted/canceled order.'', 16, 1)
    ROLLBACK
END

Esto puede parecer mucho trabajo, pero ahora puedes hacer esto:

CREATE TABLE Invoices
(
    InvoiceID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Invoices PRIMARY KEY CLUSTERED,
    OrderID int NOT NULL
        CONSTRAINT FK_Invoices_Orders FOREIGN KEY
            REFERENCES Orders (OrderID),
    InvoiceDate datetime NOT NULL
        CONSTRAINT DF_Invoices_Date DEFAULT GETDATE(),
    IsPaid bit NOT NULL
        CONSTRAINT DF_Invoices_IsPaid DEFAULT 0,
    ...
)

¿Ves lo que hice aquí? Nuestras facturas son entidades prístinas y sagradas, no contaminadas por cambios arbitrarios por parte de un tipo de servicio al cliente del primer día de trabajo. No hay riesgo de arruinarse aquí. Pero, si es necesario, aún podemos descubrir toda la "historia" de una factura porque se vincula con su Order original, que, si lo recuerda, no permitiremos cambios una vez que deje el estado activo.

Esto representa correctamente lo que está sucediendo en el mundo real. Una vez que se envía / publica una factura, no se puede recuperar. Está por ahí. Si desea cancelarlo, debe registrar una anulación, ya sea en un A / R (si su sistema admite ese tipo de cosas) o como una factura negativa para satisfacer sus informes financieros. Y si lo hace, puede ver lo que sucedió sin tener que profundizar en el historial de auditoría de cada factura; solo tiene que mirar las facturas.

Todavía existe el problema que los desarrolladores deben recordar para cambiar el estado del pedido después de que se haya publicado como una factura, pero podemos remediarlo con un activador:

CREATE TRIGGER tr_Invoices_UpdateOrderStatus
ON Invoices
FOR INSERT
AS

UPDATE Orders
SET OrderStatus = 2
WHERE OrderID IN (SELECT OrderID FROM inserted)

Ahora su información está a salvo de usuarios descuidados e incluso desarrolladores descuidados. Y las facturas ya no son ambiguas; no tiene que preocuparse por los errores que se arrastran porque alguien olvidó verificar el estado de la factura, porque no hay estado .

Así que solo para volver a resumir y parafrasear algo de esto: ¿Por qué me he tomado tantas molestias solo por algún historial de facturas?

Porque las facturas que aún no se han publicado no son transacciones reales . Son transacción "estado" - transacciones en curso. No pertenecen a sus datos transaccionales. Al mantenerlos separados de esta manera, resolverá muchos posibles problemas futuros.

Descargo de responsabilidad: Todo esto es por mi experiencia personal y no he visto todos los sistemas de facturación en el mundo. No puedo garantizar con 100% de certeza que esto sea adecuado para su aplicación particular. Solo puedo reiterar el nido de problemas que he visto como resultado de la noción de facturas "pendientes", a partir de la mezcla de datos de estado con datos transaccionales.

Al igual que con cualquier otro diseño que encuentre en Internet, debe investigar esto como una posible opción y evaluar si realmente puede funcionar para usted.

Answer 4

Por lo general, las líneas de factura no se modifican. es decir, una orden (orden de compra u orden de trabajo) se convierte en una factura. Una vez que se emite una factura, puede anularse o pueden aplicarse pagos y notas de crédito, pero eso generalmente se trata.

Su situación puede ser un poco diferente, pero creo que esta es la convención habitual: después de todo, cuando recibe la factura xyz, no espera que los datos en los que se basa el documento se alteren de ninguna manera.

En cuanto al impuesto, generalmente según mi experiencia, se almacena en el nivel de la factura y se determina en el momento en que se publica la factura.

En cuanto a las órdenes que cambian antes de convertirse en facturas, normalmente no he visto nada más complejo que la auditoría básica a nivel de base de datos, por lo general, la aplicación no expone ese historial a los usuarios.

Si desea un seguimiento de auditoría directo que sea relativamente independiente del dominio, puede consultar AutoAudit , un AutoAudit de auditoría basado en el desencadenador.

Por lo general, no tenemos "borradores de facturas". Es tentador porque tiene muchas similitudes entre pedidos y facturas. Pero en realidad, es mejor tener pedidos que no se hayan convertido en facturas en una tabla separada. Las facturas tienden a tener algunas diferencias (es decir, el cambio de estado es en realidad una transformación de una entidad a otra) y, con la integridad referencial, a veces lo único que realmente desea es que las cosas se unan a facturas "reales".

Por lo tanto, siempre tenemos PurchaseOrder, PurchaseOrderLine, Invoice y InvoiceLine. En algunos casos, el PO se ha comportado más como un carrito de compras, donde el precio no se almacena y flota con la tabla de productos y otros casos en los que son más como presupuestos de precios que deben cumplirse una vez que se transmiten al cliente. Esas sutilezas pueden ser importantes al observar el flujo de trabajo y los requisitos del negocio.