当前位置：首页 > 问答 > 正文

数据库设计进销存管理：Qt实现进销存管理系统的数据库结构与qt数据库应用

卯昂然
问答
2025-08-01 19:21:05
4

数据库设计 | 进销存管理：Qt实现进销存管理系统的数据库结构与Qt数据库应用

场景引入：小张的烦恼

"这库存数据怎么又对不上了？"小张盯着电脑屏幕上的Excel表格直挠头，作为一家小型电子配件批发商的仓库管理员，他每天都要处理上百笔进货、销售和库存调拨记录，手工记录不仅效率低下，还经常出现数据错误,导致月底盘点时总是对不上账。

"要是有一套自己的进销存系统就好了..."小张心里想着，用Qt框架配合数据库技术，完全可以开发一套适合中小企业的进销存管理系统，今天我们就来聊聊如何设计这样的数据库结构,以及如何在Qt中实现数据库操作。

数据库设计进销存管理：Qt实现进销存管理系统的数据库结构与qt数据库应用

进销存管理系统数据库设计

核心表设计

(1) 商品信息表(Product)

CREATE TABLE Product (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_code VARCHAR(20) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    category_id INTEGER,
    specification VARCHAR(100),
    unit VARCHAR(10) NOT NULL,
    purchase_price DECIMAL(10,2) NOT NULL,
    selling_price DECIMAL(10,2) NOT NULL,
    min_stock INTEGER DEFAULT 0,
    max_stock INTEGER DEFAULT 0,
    status TINYINT DEFAULT 1, -- 1:正常 0:停用
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES ProductCategory(category_id)
);

(2) 商品类别表(ProductCategory)

CREATE TABLE ProductCategory (
    category_id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_name VARCHAR(50) NOT NULL,
    parent_id INTEGER,
    level TINYINT DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    status TINYINT DEFAULT 1,
    remark TEXT,
    FOREIGN KEY (parent_id) REFERENCES ProductCategory(category_id)
);

(3) 供应商表(Supplier)

CREATE TABLE Supplier (
    supplier_id INTEGER PRIMARY KEY AUTOINCREMENT,
    supplier_name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(50),
    contact_phone VARCHAR(20),
    address TEXT,
    bank_account VARCHAR(50),
    tax_number VARCHAR(50),
    status TINYINT DEFAULT 1,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

(4) 客户表(Customer)

CREATE TABLE Customer (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(50),
    contact_phone VARCHAR(20),
    address TEXT,
    credit_rating TINYINT DEFAULT 3, -- 信用等级1-5
    status TINYINT DEFAULT 1,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

(5) 仓库表(Warehouse)

CREATE TABLE Warehouse (
    warehouse_id INTEGER PRIMARY KEY AUTOINCREMENT,
    warehouse_name VARCHAR(50) NOT NULL,
    location VARCHAR(100),
    manager VARCHAR(50),
    contact_phone VARCHAR(20),
    status TINYINT DEFAULT 1,
    remark TEXT
);

(6) 库存表(Inventory)

CREATE TABLE Inventory (
    inventory_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    warehouse_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    last_check_time DATETIME,
    UNIQUE (product_id, warehouse_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id),
    FOREIGN KEY (warehouse_id) REFERENCES Warehouse(warehouse_id)
);

(7) 采购单表(PurchaseOrder)

CREATE TABLE PurchaseOrder (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_no VARCHAR(30) NOT NULL UNIQUE,
    supplier_id INTEGER NOT NULL,
    warehouse_id INTEGER NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    order_status TINYINT DEFAULT 0, -- 0:待审核 1:已审核 2:已入库 3:已取消
    payment_status TINYINT DEFAULT 0, -- 0:未付款 1:部分付款 2:已付款
    create_user_id INTEGER,
    audit_user_id INTEGER,
    audit_time DATETIME,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id),
    FOREIGN KEY (warehouse_id) REFERENCES Warehouse(warehouse_id)
);

(8) 采购单明细表(PurchaseOrderDetail)

CREATE TABLE PurchaseOrderDetail (
    detail_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    received_quantity INTEGER DEFAULT 0,
    remark TEXT,
    FOREIGN KEY (order_id) REFERENCES PurchaseOrder(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

(9) 销售单表(SalesOrder)

CREATE TABLE SalesOrder (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_no VARCHAR(30) NOT NULL UNIQUE,
    customer_id INTEGER NOT NULL,
    warehouse_id INTEGER NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    order_status TINYINT DEFAULT 0, -- 0:待审核 1:已审核 2:已出库 3:已取消
    payment_status TINYINT DEFAULT 0, -- 0:未付款 1:部分付款 2:已付款
    create_user_id INTEGER,
    audit_user_id INTEGER,
    audit_time DATETIME,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    FOREIGN KEY (warehouse_id) REFERENCES Warehouse(warehouse_id)
);

(10) 销售单明细表(SalesOrderDetail)

CREATE TABLE SalesOrderDetail (
    detail_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    delivered_quantity INTEGER DEFAULT 0,
    remark TEXT,
    FOREIGN KEY (order_id) REFERENCES SalesOrder(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

(11) 库存调拨表(StockTransfer)

CREATE TABLE StockTransfer (
    transfer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    transfer_no VARCHAR(30) NOT NULL UNIQUE,
    from_warehouse_id INTEGER NOT NULL,
    to_warehouse_id INTEGER NOT NULL,
    total_quantity INTEGER NOT NULL,
    transfer_status TINYINT DEFAULT 0, -- 0:待调拨 1:已调出 2:已调入 3:已取消
    create_user_id INTEGER,
    audit_user_id INTEGER,
    audit_time DATETIME,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_warehouse_id) REFERENCES Warehouse(warehouse_id),
    FOREIGN KEY (to_warehouse_id) REFERENCES Warehouse(warehouse_id)
);

(12) 库存调拨明细表(StockTransferDetail)

CREATE TABLE StockTransferDetail (
    detail_id INTEGER PRIMARY KEY AUTOINCREMENT,
    transfer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    out_quantity INTEGER DEFAULT 0,
    in_quantity INTEGER DEFAULT 0,
    remark TEXT,
    FOREIGN KEY (transfer_id) REFERENCES StockTransfer(transfer_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

(13) 库存盘点表(StockCheck)

CREATE TABLE StockCheck (
    check_id INTEGER PRIMARY KEY AUTOINCREMENT,
    check_no VARCHAR(30) NOT NULL UNIQUE,
    warehouse_id INTEGER NOT NULL,
    check_user_id INTEGER,
    check_time DATETIME,
    check_status TINYINT DEFAULT 0, -- 0:进行中 1:已完成
    profit_loss_amount DECIMAL(12,2) DEFAULT 0,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (warehouse_id) REFERENCES Warehouse(warehouse_id)
);

(14) 库存盘点明细表(StockCheckDetail)

CREATE TABLE StockCheckDetail (
    detail_id INTEGER PRIMARY KEY AUTOINCREMENT,
    check_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    system_quantity INTEGER NOT NULL,
    actual_quantity INTEGER NOT NULL,
    difference INTEGER NOT NULL,
    difference_amount DECIMAL(12,2),
    remark TEXT,
    FOREIGN KEY (check_id) REFERENCES StockCheck(check_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

(15) 用户表(User)

CREATE TABLE User (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    real_name VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    role_id INTEGER,
    status TINYINT DEFAULT 1, -- 1:启用 0:禁用
    last_login_time DATETIME,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES Role(role_id)
);

(16) 角色表(Role)

CREATE TABLE Role (
    role_id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_name VARCHAR(50) NOT NULL,
    permissions TEXT, -- 存储权限JSON
    status TINYINT DEFAULT 1,
    remark TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

(17) 操作日志表(OperationLog)

CREATE TABLE OperationLog (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    module VARCHAR(50),
    operation VARCHAR(50),
    method VARCHAR(100),
    params TEXT,
    ip VARCHAR(50),
    operation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TINYINT DEFAULT 1, -- 1:成功 0:失败
    error_msg TEXT,
    FOREIGN KEY (user_id) REFERENCES User(user_id)
);

数据库关系图

整个数据库的关系可以描述为：

商品(Product)属于某个类别(ProductCategory)，一个类别可以有多个商品
商品存放在仓库(Warehouse)中，库存信息记录在Inventory表中
采购单(PurchaseOrder)从供应商(Supplier)采购商品，明细记录在PurchaseOrderDetail中
销售单(SalesOrder)向客户(Customer)销售商品，明细记录在SalesOrderDetail中
库存可以在仓库间调拨(StockTransfer)，明细记录在StockTransferDetail中
定期进行库存盘点(StockCheck)，差异记录在StockCheckDetail中
用户(User)拥有特定角色(Role)，操作记录在OperationLog中

Qt中的数据库应用

数据库连接配置

在Qt中，我们通常使用QSqlDatabase来管理数据库连接，对于进销存系统，SQLite是一个轻量级的选择，适合单机版应用；如果需要多用户访问,可以考虑MySQL或PostgreSQL。

数据库设计进销存管理：Qt实现进销存管理系统的数据库结构与qt数据库应用

// 示例：SQLite数据库连接
bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("inventory.db");
    if (!db.open()) {
        QMessageBox::critical(nullptr, QObject::tr("无法打开数据库"),
            QObject::tr("无法建立数据库连接，\n"
                       "点击取消退出。"), QMessageBox::Cancel);
        return false;
    }
    return true;
}
// 示例：MySQL数据库连接
bool createMySQLConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("inventory");
    db.setUserName("root");
    db.setPassword("password");
    if (!db.open()) {
        QMessageBox::critical(nullptr, QObject::tr("无法打开数据库"),
            QObject::tr("无法建立数据库连接，\n"
                       "点击取消退出。"), QMessageBox::Cancel);
        return false;
    }
    return true;
}

数据库初始化与表创建

系统首次运行时需要初始化数据库和表结构：

void initDatabase()
{
    QSqlQuery query;
    // 创建商品类别表
    query.exec("CREATE TABLE IF NOT EXISTS ProductCategory ("
               "category_id INTEGER PRIMARY KEY AUTOINCREMENT,"
               "category_name VARCHAR(50) NOT NULL,"
               "parent_id INTEGER,"
               "level TINYINT DEFAULT 1,"
               "sort_order INTEGER DEFAULT 0,"
               "status TINYINT DEFAULT 1,"
               "remark TEXT,"
               "FOREIGN KEY (parent_id) REFERENCES ProductCategory(category_id))");
    // 创建其他表...
    // 这里省略其他表的创建语句，实际应用中应该完整实现
    // 插入初始数据
    query.exec("INSERT OR IGNORE INTO ProductCategory (category_name) VALUES ('默认类别')");
    query.exec("INSERT OR IGNORE INTO Role (role_name, permissions) VALUES ('管理员', '{}')");
    query.exec("INSERT OR IGNORE INTO User (username, password, real_name, role_id) "
               "VALUES ('admin', '123456', '系统管理员', 1)");
}

商品管理模块实现

(1) 商品模型类

class ProductModel : public QSqlTableModel
{
    Q_OBJECT
public:
    explicit ProductModel(QObject *parent = nullptr, QSqlDatabase db = QSqlDatabase::database())
        : QSqlTableModel(parent, db)
    {
        setTable("Product");
        setEditStrategy(QSqlTableModel::OnManualSubmit);
        select();
    }
    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const override
    {
        if (role == Qt::TextAlignmentRole) {
            return int(Qt::AlignVCenter | Qt::AlignLeft);
        }
        return QSqlTableModel::data(index, role);
    }
    bool addProduct(const QString &code, const QString &name, int categoryId, 
                   const QString &spec, const QString &unit, 
                   double purchasePrice, double sellingPrice)
    {
        QSqlRecord record = this->record();
        record.setValue("product_code", code);
        record.setValue("product_name", name);
        record.setValue("category_id", categoryId);
        record.setValue("specification", spec);
        record.setValue("unit", unit);
        record.setValue("purchase_price", purchasePrice);
        record.setValue("selling_price", sellingPrice);
        record.setValue("status", 1);
        record.setValue("create_time", QDateTime::currentDateTime());
        record.setValue("update_time", QDateTime::currentDateTime());
        if (!insertRecord(rowCount(), record)) {
            qDebug() << "添加商品失败:" << lastError().text();
            return false;
        }
        if (!submitAll()) {
            qDebug() << "提交商品失败:" << lastError().text();
            return false;
        }
        return true;
    }
    bool updateProduct(int productId, const QVariantMap &data)
    {
        for (int i = 0; i < rowCount(); ++i) {
            if (this->data(this->index(i, 0)).toInt() == productId) {
                for (auto it = data.begin(); it != data.end(); ++it) {
                    setData(index(i, fieldIndex(it.key())), it.value());
                }
                setData(index(i, fieldIndex("update_time")), QDateTime::currentDateTime());
                if (!submitAll()) {
                    qDebug() << "更新商品失败:" << lastError().text();
                    return false;
                }
                return true;
            }
        }
        return false;
    }
    bool deleteProduct(int productId)
    {
        for (int i = 0; i < rowCount(); ++i) {
            if (this->data(this->index(i, 0)).toInt() == productId) {
                if (!removeRow(i)) {
                    qDebug() << "删除商品失败:" << lastError().text();
                    return false;
                }
                if (!submitAll()) {
                    qDebug() << "提交删除失败:" << lastError().text();
                    return false;
                }
                return true;
            }
        }
        return false;
    }
    QList<QVariantMap> getProductsByCategory(int categoryId)
    {
        QList<QVariantMap> products;
        QString filter = categoryId > 0 ? QString("category_id = %1").arg(categoryId) : "";
        setFilter(filter);
        select();
        for (int i = 0; i < rowCount(); ++i) {
            QVariantMap product;
            QSqlRecord record = this->record(i);
            for (int j = 0; j < record.count(); ++j) {
                product.insert(record.fieldName(j), record.value(j));
            }
            products.append(product);
        }
        return products;
    }
};

(2) 商品界面实现

class ProductWidget : public QWidget
{
    Q_OBJECT
public:
    explicit ProductWidget(QWidget *parent = nullptr)
        : QWidget(parent), productModel(new ProductModel(this))
    {
        setupUI();
        connectSignals();
        loadCategories();
    }
private:
    void setupUI()
    {
        QVBoxLayout *mainLayout = new QVBoxLayout(this);
        // 搜索区域
        QHBoxLayout *searchLayout = new QHBoxLayout;
        categoryCombo = new QComboBox;
        searchEdit = new QLineEdit;
        searchEdit->setPlaceholderText("输入商品名称或编码搜索");
        QPushButton *searchBtn = new QPushButton("搜索");
        QPushButton *addBtn = new QPushButton("添加商品");
        searchLayout->addWidget(new QLabel

本文由卯昂然于2025-08-01发表在【云服务器提供商】，文中图片由（卯昂然）上传，本平台仅提供信息存储服务；作者观点、意见不代表本站立场，如有侵权，请联系我们删除；若有图片侵权，请您准备原始证明材料和公证书后联系我方删除！
本文链接：https://vps.7tqx.com/wenda/508120.html