SQL Server借贷凭证表结构选型咨询：Voucher与Voucher_2该选哪一个？

Hey there! Let's break this down to figure out which voucher table structure fits your lending/borrowing database better. Since I don't have the exact schema for Voucher and Voucher_2, I'll walk you through key evaluation factors and common SQL Server best practices for this domain to help you make the call.

1. Alignment with Core Lending/Borrowing Business Logic #

First, clarify what a "voucher" represents in your project:

• Is it a single document tied to one specific transaction (e.g., a single disbursement entry)?
• Or is it a batch container for multiple related transactions (e.g., a repayment voucher covering principal + interest + fee entries)?

Ask yourself: Which table's structure maps directly to how vouchers are used in your business workflow? For example, if your process requires grouping multiple transactions under one voucher (like a monthly loan repayment), a structure that supports 1:many relationships with Transactions is non-negotiable.

2. Schema Normalization & Data Integrity #

Normalization Level #

Check how each table adheres to normal forms:

• Does Voucher_2 eliminate redundant data (e.g., not storing total debit/credit values that can be calculated from Transactions)? That’s 3NF compliance, which reduces data inconsistency risks.
• Does Voucher store aggregated values (like TotalDebit/TotalCredit)? This adds redundancy but might speed up frequent summary queries—just ensure you have safeguards (triggers, stored procedures) to keep these values in sync with linked transactions.

Enforcing Lending Rules #

Lending databases rely on the principle "every debit has a matching credit". Which table makes it easier to enforce this?

• If Voucher includes TotalDebit and TotalCredit fields, you can add a CHECK (TotalDebit = TotalCredit) constraint to guarantee balance at the voucher level.
• If Voucher_2 only stores metadata (voucher number, date, creator), you’ll need to use triggers or application logic to validate that sum of linked transaction debits equals credits.

3. Query Performance & Usability #

Think about the most frequent queries you’ll run:

• Do you often need to pull a voucher’s total amount without joining to Transactions? A Voucher table with precomputed totals will be faster for these cases.
• Do you mostly query transaction details tied to a voucher? A normalized Voucher_2 will simplify joins and avoid redundant data storage, which is cleaner for smaller datasets (common in university projects).

4. Extensibility for Future Requirements #

Consider if your project might need to add features later:

• Will you need to track voucher status (approved, pending, rejected), attach document links, or add custom fields for different voucher types (loan disbursement, interest adjustment)?
• Which table’s structure makes adding these fields easier without breaking existing relationships or queries? A leaner Voucher_2 might be more flexible here, as it doesn’t lock you into precomputed fields.

Example Scenario to Guide You #

Suppose your two tables look like this:

Voucher Schema #

CREATE TABLE Voucher (
    VoucherID INT PRIMARY KEY IDENTITY(1,1),
    VoucherNo VARCHAR(20) UNIQUE NOT NULL,
    VoucherDate DATE NOT NULL,
    TotalDebit DECIMAL(18,2) NOT NULL,
    TotalCredit DECIMAL(18,2) NOT NULL,
    CreatedBy VARCHAR(50) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE(),
    CHECK (TotalDebit = TotalCredit)
)

Voucher_2 Schema #

CREATE TABLE Voucher_2 (
    VoucherID INT PRIMARY KEY IDENTITY(1,1),
    VoucherNo VARCHAR(20) UNIQUE NOT NULL,
    VoucherDate DATE NOT NULL,
    CreatedBy VARCHAR(50) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE()
)

• Choose Voucher if you need fast access to voucher totals and want to enforce balance at the database level (use triggers to sync totals with Transactions).
• Choose Voucher_2 if you prioritize clean, normalized data and don’t mind joining to Transactions for summaries (ideal for smaller project datasets).

Start by mapping your exact business workflow to each table’s structure. If you’re still unsure, build a small test dataset with both schemas, run your most common queries, and see which feels more intuitive and efficient for your project’s needs.

内容的提问来源于stack exchange，提问作者Ghufran Ataie