用于Web内部交换平台的SQL数据库设计 [英] SQL database design for web internal exchange platform

问题描述

我正在使用一个允许用户互相交易信用的小型Web应用程序。我想对我的初始设计（效率，灵活性，安全性等）发表评论。

特别是，典型的事务发生如下：

用户A可以提供交换X学分的工作。用户B和C可以接受报价。从B和C的帐户中减去总共X个学分。工作完成后，X的学分将被添加到A的账户。 A可以使用获得的信用额与其他用户进行交易。

我正在考虑一个具有3个表的数据库：作业，事务和帐户。帐户表将跟踪每个用户获得的信用和支出。

每次用户A为用户B和C完成工作：

• 两个交易记录在交易表中：从B转移到A，从C转移到A




• 账户表将被更新：可用信用额将在A，B和C的帐户中更新




• 工作表也已更新。

（所有这些操作都将被包装在一个SQL事务中，即所有记录都被更新或没有）

当一个交易被恢复（例如，在A和B之间但不在A和C之间）时，A和B的帐户和Account_History将被更新，A和B之间的交易将被设置为还原。

Rails中的草图如下：

• class Job

  
  
  

  属性：：job_id，：total_credit，：created_at，：updated_at

  
  
  

  belongs_to：seller，class_name =>User belongs_to：buyer，
  ：class_name =>User＃一个工作可以为多个用户完成




• class Transaction

  
  
  

  属性：transaction_id，：buyer_id，：seller_id，：status，
  
  ：credit，：created_at，update_at

  
  
  

  belongs_to：seller，class_name =>Userbelongs_to：buyer，
  
  ：class_name =>每个信用互换的用户＃个交易记录
  
  belongs_to：account




• 类帐户

  
  
  

  属性：：account_id，：user_id，，available_credit

  
  
  

  belongs_to：user

我不知道我是否应该：

1. 创建Account_History表以显示从帐户表提取的摘要统计信息
   （如上个月交易，总收益
   ） /花费等），或将这些信息存储在一系列
   的缓存查询中。

   
   
   

   表示一个待处理的事务（即当B接受A的报价，但
   工作尚未完成）和完成的交易（即B
   接受A的工作）在Transaction表中的分离记录中。
   其他选项只是将交易的状态从
   等待更改为完成。

感谢您的意见。

解决方案

更传统的方法是使用更多的双重会计设计。 / p>

您可以在任何地方阅读有关此设计的信息，但这里是指向相当不错的讨论它是什么以及它的工作原理。

您的设计结果将会就是你想要一个单独的表将两个你的示例事务绑定到单个工作单元中。您需要一个表，每个业务事务具有单个条目，另一个表是该事务涉及的每个帐户的一个条目。在引用的链接中，这些是JOURNAL和POSTING表。

在这种设计中，可用的积分不会存储在帐户中，除非你想这样做作为非规范化。通常，余额总是计算在内。

您可以决定拥有帐户余额历史记录表以进行报告，但如果您的系统允许对旧的交易进行编辑（如果它是传统的会计系统，那么它不会）。

如果你想区分待处理的事务与已经完成的事务之间的关系，那么可以在事务处理（在引用的链接JOURNAL表中）放置一个状态标志。然而，还有一个更为全面的会计惯例，它将为每个用户提供两个账户，一个是获得的信用额度和一个未被授予的信用额度之一。在这个方案中，使用你的例子，B和C将把一些信用额度放入A的等待信用账户。这将立即创建B和C的帐户，这样他们就不会在其他地方花费这些积分。当A完成工作后，这些积分将从A的待处理帐户移动到A的当前账户。如果工作被取消，您将投入一笔交易从A的待处理帐户转回B和C的当前账户。

这样可能看起来有点复杂，但它有明显的优势，总是存在发生的一切记录。

I am working on a small web application that allows users to trade credit to each other. I would like to have your comments on my initial design (efficiency, flexibility, security etc.)

In particular, a typical transaction happens as follow:

User A can offer to do a job in exchange for X credits. Users B and C can accept the offer. A total of X credits will be subtracted from B and C's accounts. When the job is completed, X credits will be added to A's account. A can use earned credits to trade with other users.

I am thinking of a database with 3 tables: Job, Transaction and Account. Account table will keep track of credit earned and spent for each user.

Each time a job is done for user B and C by user A:

• Two transactions are recorded in Transaction table: a transfer from B to A, and a transfer from C to A

• Account table will be updated: available credits will be updated in A, B and C's accounts

• Job table is also updated.

(All these actions will be wrapped in an SQL "transaction", i.e., all records are updated, or none)

When a transaction is reverted (say, between A and B, but not between A and C), A and B's Account and Account_History will be updated, transaction between A and B will be set to "revert".

A sketch in Rails goes as follow:

• class Job

  attributes: :job_id, :total_credit,:created_at, :updated_at

  belongs_to :seller, :class_name => "User" belong_to :buyers, :class_name => "User" # one job may be done for multiple users

• class Transaction

  attributes: transaction_id, :buyer_id, :seller_id, :status,
  :credit, :created_at, :updated_at

  belongs_to :seller, :class_name => "User" belongs_to :buyer,
  :class_name => "User # one transaction record per credit swap
  belongs_to :account

• class Account

  attributes: :account_id, :user_id, :available_credit

  belongs_to: user

I am not sure if I should:

1. Create Account_History table to present summary statistics extracted from Account table (such as last month transactions, total credit earned/spent etc.) to customers, or to store these info in a series of cached queries.

   Represent a pending transaction (i.e., when B accepts A's offer but the job is not done yet) and a completed transaction (i.e., when B accepts A's job) in separated records in Transaction table. The other option would be just to change status of a transaction from pending to completed.

Thanks for your comments.

解决方案

A more conventional approach would be to use more of a double-entry accounting design.

You can read about this design just about anywhere, but here is a link to a pretty good discussion of what it is and how it works.

The upshot for your design would be that you want a separate table to tie the two pieces of your example transaction into a single unit of work. You want one table that has a single entry per business transaction and another table that one entry for each account involved in that transaction. In the referenced link, these are the JOURNAL and POSTING tables.

In this kind of design, the available credits are not stored in the account, unless you want to do so as a denormalization. Typically the balance is always calculated.

You could decide to have an account balance history table for reporting purposes, but you will need to be sure to handle recalculating this history if your system allows for old transactions to be edited (which it wouldn't, if it were a conventional accounting system).

If you want to differentiate between transactions that are pending versus those that have been completed, then you can put a status flag on the transaction (in the referenced link, the JOURNAL table). However, there is also a more full-blown accounting convention that would give each user two accounts, one of earned credits and one of unearned credits. In this scheme, using your example, B and C would put some credits into A's pending credit account. This would create draws on B's and C's accounts right away, so that they don't spend those credits elsewhere. When A completes the job, those credits get moved from A's pending account to A's current account. If the job gets cancelled, you'd put in a transaction to transfer back from A's pending account to B's and C's current accounts.

This way may seem a bit more complicated, but it has the distinct advantage of there always being a record of everything that has happened.

这篇关于用于Web内部交换平台的SQL数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！