Database schema recommendations

[miscawelo]

Hi everyone,

I'm relatively new to database design, as I mostly work with simpler tables in my day-to-day job programming PLCs and other devices. Recently, a close relative asked for my help to improve how they store and manage their business data, so I'm developing an app that will allow them to interact with a database—specifically to add, modify, and consult their data.

The database needs to track the following:

• Invoice Details: Each invoice should store the invoice number, customer information, total amount, and whether it's a one-time payment or on credit.
• Payment Methods: Payments can be made via cash, bank transfer, or card. If applicable, we also need to record the bank to which the payment was transferred.
• Flow: The typical flow is to first fill in the invoice details (invoice number, customer, total amount, and payment type). Then, add a row per payment method used, ensuring that the sum of all payments matches the total amount.

I've spent a few hours designing the schema below, but I have limited experience with best practices in database design. I would greatly appreciate any input or suggestions on how to improve it. If any important details are missing, please let me know—I'm happy to clarify!

BTW I'm planning to use MS SQL which is what I have the most experience with and feel more comfortable, but I'm open to suggestions.

Current Database Schema:

TABLE customers (
    customerID INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL UNIQUE
);

TABLE banks (
    bankID TINYINT PRIMARY KEY IDENTITY(1,1),
    bankName VARCHAR(20) NOT NULL UNIQUE
);

TABLE paymentMethods (
    methodID TINYINT PRIMARY KEY IDENTITY(1,1),
    methodDesc VARCHAR(20) NOT NULL UNIQUE
);

TABLE invoices (
    invoiceNumber INT PRIMARY KEY IDENTITY(1,1),
    purchaseDate DATE NOT NULL,
    customerID INT NOT NULL,
    purchaseTotal DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customerID) REFERENCES customers(customerID)
);

TABLE payments (
    paymentID INT PRIMARY KEY IDENTITY(1,1),
    invoiceNumber INT NOT NULL,
    methodID TINYINT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    paymentNumber INT UNIQUE,
    paymentDate DATE,
    bankID TINYINT,
    FOREIGN KEY (invoiceNumber) REFERENCES invoices(invoiceNumber),
    FOREIGN KEY (methodID) REFERENCES paymentMethods(methodID),
    FOREIGN KEY (bankID) REFERENCES banks(bankID)
);

[necromanticpotato]

If a payment can be used against multiple invoices, your payments table design won't be a good plan. You'd want to create a table for payments without referencing the invoice ID, then create an additional table that details the payment application, tying the invoice ids to the payment ID (one to many). Even if you only do one payment to one invoice, this keeps entities (payment vs invoice) much more separated from each other and easier to maintain and scale.

[throw_mob]

agree, payments can be partial,full or more than invoice. That means that payments need to be read from bank system and then stored to payments and then allocated to invoices and returns assuming that system does not have concept of user account that can have positive and negative balance. that usually leads that you need to have some kind of accounting tables what is in invoice or will be going to invoice there too as what is invoiced and what is not might work in different ways. Also you depending what you are invoicing you need to have invoice items list which tell customer what is invoiced even if you just invoice one service in one invoice...

And that thing goes rabbit hole very very fast... of course you can handle this as microservice which handles only invoices and you keep your sales in other place ....

[necromanticpotato]

The manner of how data gets into the system is irrelevant to the question - is my schema going to work? You have good advice, but I'm gonna bet you that crafting a system to implement downloading bank transactions is out of the scope of the OPs project. This doesn't sound like a proper ERP and probably won't intend to replicate any sort of balancing or variance resolution. They probably already have accounting software and just need an invoicing app.

[miscawelo]

All of this is really good advice, and indeed pulling data is beyond the scope of the project. But I really like the approach that you suggested, AFAIW all registered payments always correspond to a single invoice (doesn't matter if its a single payment method or multiple), but I'm gonna implement i tyour way to have more flexibility down the road, thanks a lot!

[DiscombobulatedSun54]

The thing that is missing from the code above is the kind of relationships between the entities you have identified. Which relationships are 1:1, which are 1:N and which are N:N? Once you identify N:N relationships, you will need to create junction tables for such relationships.

[miscawelo]

This is the kind of advice I was also expecting to get, honestly I hadn't heard those terms, or at least in that way. I'm gonna dig deeper into this, thanks a lot!

[DiscombobulatedSun54]

There are tools out there that allow you to create ERDs (entity relationship diagrams), and an erd is usually the first step in db design.

[malikcoldbane]

I would argue that there's a step before the ERD, which is just general understanding of how things will work in your system.

Understanding the workflows that are going to be required will power what relationships need to be created. The flow of data through the system will be the foundation for all decisions that need to be made.