sourcetip

송장, 송장 라인 및 수정사항에 대한 데이터베이스 설계

fileupload 2023. 8. 21. 21:32
반응형

송장, 송장 라인 및 수정사항에 대한 데이터베이스 설계

저는 프랜차이즈의 CRM(리팩터링이 많은)을 위한 관계형 데이터베이스의 두 번째 주요 반복을 설계하고 있으며, 각 송장에 변경된 사항에 대한 강력한 감사 추적과 함께 작업 송장 및 송장 라인을 저장하기 위한 최상의 데이터베이스 설계 관행에 대한 도움이 필요합니다.

현재 스키마

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))

InvoiceLines

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

개정 스키마

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)

스키마 설계 고려 사항

송장의 지불됨 또는 보류 중 상태를 저장하는 것이 현명합니까?

에 됩니다.Payments 예금현금, 신용카드, 수표, 은행 예금)."를 "유급" 로 저장하는 것이 ?Invoices 만약과 관련된 이 진직의관련모된든수입표추수론경있우에서 될 수 .Payments 테이블?

송장 라인 항목의 수정사항을 추적하는 방법은 무엇입니까?

송장 수정 표에 상태 변경사항과 함께 송장 합계 및 감사 사용자를 저장하여 송장 수정사항을 추적할 수 있습니다( 참조).InvoiceRevisions위)를 참조하십시오. 그러나 송장 라인 수정 표를 추적하는 것은 유지하기가 어렵습니다.생각은?편집: 라인 항목은 변경할 수 없습니다.이것은 "초안" 송장에 적용됩니다.

세금

송장 데이터를 저장할 때 판매세(또는 SA의 14% VAT)를 어떻게 통합해야 합니까?

편집: 좋은 피드백입니다, 여러분.송장 및 송장 라인은 정의상 불변이므로 변경 사항을 추적하는 것은 바람직하지 않습니다.그러나 "초안" 송장은 발행하기 전에 둘 이상의 사용자가 편집할 수 있어야 합니다(예: 정비사가 송장을 작성한 후 관리자가 할인 적용).

송장 상태를 정의하고 추적하는 가장 좋은 방법은 무엇입니까?

  1. 초안
  2. 발행된
  3. 보이드

...한 방향으로 변화하도록 제한되어 있습니까?

다른 사람이 설계한 송장 처리 시스템의 백엔드로 작업해야 했던 약 4년간의 조언:송장에 "보류" 상태가 없습니다.그것은 당신을 미치게 할 것입니다.

보류 중인 송장을 일반 송장으로("보류 중" 플래그/상태 표시) 저장할 때 문제는 게시된 송장만 고려해야 하는 작업/보고서가 수백 개가 된다는 것입니다. 이는 문자 그대로 보류 중을 제외한 모든 상태를 의미합니다.즉, 이 상태를 매번 확인해야 합니다.그리고 누군가는 잊어버릴 것입니다.그리고 그것을 깨닫는 데는 몇 주가 걸릴 것입니다.

다음을 생성할 수 있습니다.ActiveInvoices보류 중인 필터가 내장된 보기는 문제를 이동시킬 뿐입니다. 누군가가 테이블 대신 보기를 사용하는 것을 잊어버립니다.

보류 중인 송장은 송장이 아닙니다.질문 코멘트에 초안(또는 주문, 요청 등, 모두 동일한 개념)으로 올바르게 기재되어 있습니다.이러한 초안을 수정할 수 있어야 하는 필요성은 확실히 이해할 수 있습니다.그래서 여기 제가 추천할 만한 것이 있습니다.

표를 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 (...)

기본 "초안" 표입니다.그들은 바뀔 수 있습니다.사항을 의 " 사항추적원면있본모는에열든있기합테만니다야들을어이블록는변경이을"에 열이 합니다.Orders그리고.OrderDetails테이블과 마지막으로 수정한 사용자, 날짜 및 수정 유형(삽입, 업데이트 또는 삭제)에 대한 감사 열이 있습니다.

Cade가 언급했듯이 AutoAudit을 사용하여 이 프로세스의 대부분을 자동화할 수 있습니다.

또한 더 이상 활성화되지 않은 미발송 문서(특히 게시되어 송장이 된 미발송 문서)에 대한 업데이트를 방지하는 트리거도 사용할 수 있습니다.이 데이터를 일관성 있게 유지하는 것이 중요합니다.

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

송장은 2단계 계층이므로 세부 정보에 대해 유사하고 약간 더 복잡한 트리거가 필요합니다.

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

이것은 많은 일처럼 보일 수 있지만, 이제 여러분은 이것을 할 수 있습니다.

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,
    ...
)

제가 여기서 뭘 했는지 보세요?우리의 송장은 순수하고 신성한 실체이며, 근무 첫날 고객 서비스 직원의 자의적인 변경에도 흔들리지 않습니다.여기서 실수할 위험은 없습니다.하지만 필요하다면 송장이 원본과 다시 연결되기 때문에 송장의 전체 "이력"을 찾을 수 있습니다.Order기억하시겠지만, 활성 상태를 떠난 후에는 변경을 허용하지 않습니다.

이것은 현실 세계에서 일어나는 일을 정확하게 표현합니다.송장은 한 번 발송/게시되면 반송할 수 없습니다.그건 밖에 있어.취소하려면 A/R(시스템에서 해당 유형을 지원하는 경우) 또는 재무 보고를 충족하기 위해 마이너스 송장으로 반전을 게시해야 합니다.이렇게 하면 각 송장의 감사 내역을 파헤치지 않고도 실제로 무슨 일이 일어났는지 확인할 수 있습니다. 송장 자체를 확인하기만 하면 됩니다.

개발자들이 송장으로 게시된 후 주문 상태를 변경해야 하는 문제가 여전히 남아 있지만, 우리는 트리거를 통해 이 문제를 해결할 수 있습니다.

CREATE TRIGGER tr_Invoices_UpdateOrderStatus
ON Invoices
FOR INSERT
AS

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

이제 데이터는 부주의한 사용자와 부주의한 개발자로부터도 안전합니다.송장은 더 이상 모호하지 않습니다. 누군가 송장 상태를 확인하는 것을 잊어버렸기 때문에 벌레가 슬금슬금 들어오는 것에 대해 걱정할 필요가 없습니다. 상태가 없기 때문입니다.

이 중 일부를 다시 요약하고 바꿔 말하면 다음과 같습니다.왜 제가 송장 내역 때문에 이 모든 문제를 겪었습니까?

왜냐하면 아직 게시되지 않은 송장은 실제 거래가 아니기 때문입니다.트랜잭션 "상태" - 진행 중인 트랜잭션입니다.해당 데이터는 사용자의 트랜잭션 데이터에 속하지 않습니다.이렇게 분리해서 보관함으로써 미래에 발생할 수 있는 많은 문제들을 해결할 수 있습니다.

고지 사항:이것은 모두 제 개인적인 경험에서 나온 것이며 전 세계의 모든 송장 처리 시스템을 본 적이 없습니다.저는 이것이 당신의 특정한 용도에 적합하다고 100% 확신할 수 없습니다.저는 주 데이터와 트랜잭션 데이터를 혼합하여 송장을 "보류"한다는 개념에서 비롯된 문제의 집합체를 반복할 수밖에 없습니다.

인터넷에서 찾을 수 있는 다른 모든 설계와 마찬가지로 가능한 옵션 중 하나로 이를 조사하고 실제로 효과가 있는지 여부를 평가해야 합니다.

일반적으로 송장 라인은 변경되지 않습니다. 즉, 주문(구매 주문 또는 작업 주문)이 송장이 됩니다.일단 송장이 발행되면 무효가 되거나 결제와 신용메모를 신청할 수 있지만, 보통은 그 정도입니다.

당신의 상황은 조금 다를 수 있지만, 저는 이것이 일반적인 관례라고 생각합니다 - 결국, 당신이 인보이스 xyz를 받았을 때, 당신은 문서의 기초가 된 데이터가 어떤 식으로든 변경될 것이라고 기대하지 않습니다.

일반적으로 제 경험에 비추어 볼 때, 송장 수준에서 보관되고 송장이 게시될 때 결정되는 세금에 대해서는,

송장이 되기 전에 변경된 주문에 대해서는 일반적으로 기본적인 데이터베이스 수준 감사보다 더 복잡한 것은 없습니다. 일반적으로 애플리케이션은 해당 내역을 사용자에게 노출하지 않습니다.

상대적으로 도메인에 구애받지 않는 간단한 감사 추적을 원하는 경우 트리거 기반 감사 추적인 AutoAudit을 조사할 수 있습니다.

일반적으로 "청구서 초안"은 없습니다.당신은 주문과 송장이 비슷해서 유혹적입니다.하지만 실제로는 송장이 되지 않은 주문은 별도의 표에 기재하는 것이 좋습니다.송장에는 몇 가지 차이점이 있는 경향이 있습니다(예: 상태 변화는 실제로 한 실체에서 다른 실체로 전환되는 것입니다). 그리고 참조 무결성을 가진 경우에는 "실제" 송장에만 관련되기를 원하는 경우가 있습니다.

그래서 우리는 보통 구매 주문, 구매 주문 라인, 송장 및 송장 라인을 항상 가지고 있습니다.어떤 경우에는 PO 측이 쇼핑 카트처럼 행동하도록 했습니다. 가격이 저장되지 않고 제품 테이블과 함께 떠다니는 경우도 있고, 고객에게 전송된 가격 견적서와 유사한 경우도 있습니다.이러한 세부 사항은 비즈니스 워크플로우 및 요구 사항을 살펴볼 때 중요할 수 있습니다.

감사할 테이블 복사본을 만들고 원래 테이블에 있는 것보다 모든 삽입, 업데이트, 삭제 시 테이블 복사본에 행을 복사하는 트리거를 만드는 것이 어떻습니까?

트리거는 일반적으로 다음과 같습니다.

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

인보이스의 "불변성"에 대한 위 Aaronaught의 언급에 동의합니다.

만약 당신이 그 조언을 받아들인다면, 저는 "Pending Review", "Approved", "Void"를 상태로 두는 것을 고려할 것입니다."Pending Review"는 바로 그것입니다."승인"은 정확한 것으로 간주되며, 고객이 지불해야 합니다."Voided"라는 것은 송장이 더 이상 유효하지 않으며 고객이 지불할 수 없다는 것입니다.그러면 당신은 다음의 기록에서 송장이 전액 지불되었는지 여부를 추론할 수 있습니다.Payments그리고 당신은 정보를 반복하지 않습니다.

그것을 제외하고, 당신의 수정 아이디어에는 실질적인 문제가 없습니다.

세금을 단지 다른 기록으로 포함할 수 있습니다.InvoiceLines.

언급URL : https://stackoverflow.com/questions/2679333/database-design-for-invoices-invoice-lines-revisions

반응형

'sourcetip' 카테고리의 다른 글

sqlalchemy: 여러 테이블을 하나의 쿼리로 조인하는 방법은 무엇입니까?  (0) 2023.08.21
XML을 사용하여 ActionBar Activity의 ActionBar 배경색을 변경하려면 어떻게 해야 합니까?  (0) 2023.08.21
SQL 서버 실제 데이터 유형, C#에 해당하는 것은 무엇입니까?  (0) 2023.08.21
event.preventDefault()를 사용한 후 이벤트를 트리거하는 방법  (0) 2023.08.21
IMG dir는 db에 저장할 수 없지만 쿼리에 사용된 동일한 변수에서 볼 수 있습니다.  (0) 2023.08.21

'sourcetip'의 다른글

  • 현재글송장, 송장 라인 및 수정사항에 대한 데이터베이스 설계

관련글

댓글

티스토리툴바