Web内部交换平台的SQL数据库设计
我正在开发一个小型网络应用程序，允许用户相互进行信用交易。我想听听您对我的初始设计（效率、灵活性、安全性等）的评论。
特别是，典型的交易发生如下：
用户 A 可以提出做某项工作以换取 X 积分。用户 B 和 C 可以接受该要约。 B 和 C 的账户总共会扣除 X 个积分。工作完成后，X 个积分将添加到 A 的帐户中。 A 可以使用赚取的积分与其他用户进行交易。
我正在考虑一个包含 3 个表的数据库：Job、Transaction 和 Account。帐户表将跟踪每个用户获得和花费的信用。
用户A每次为用户B和C完成一项工作时：
Transaction表中记录两笔交易：从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
属于：卖家，：class_name => “用户”属于：买家， ：类名=> "User" # 一项工作可以为多个用户完成
class Transaction
属性：transaction_id、:buyer_id、:seller_id、:status、
:credit、:created_at、:updated_at属于：卖家，：class_name => “用户”属于：买家，
：类名=> “用户 # 每次信用互换有一条交易记录
belongs_to :account类帐户
属性：:account_id、:user_id、:available_credit
belongs_to: user
我不确定是否应该：
创建 Account_History 表以显示提取的摘要统计信息 来自帐户表（例如上个月的交易、总信用额 赚取/花费等）给客户，或将这些信息存储在一系列中 缓存的查询。
代表一项待处理的交易（即，当 B 接受 A 的要约但 工作尚未完成）和已完成的事务（即，当 B 接受 A 的工作）在事务表中的单独记录中。这 其他选择只是更改交易状态 待处理到已完成。
感谢您的评论。
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 usersclass Transaction
attributes: transaction_id, :buyer_id, :seller_id, :status,
:credit, :created_at, :updated_atbelongs_to :seller, :class_name => "User" belongs_to :buyer,
:class_name => "User # one transaction record per credit swap
belongs_to :accountclass Account
attributes: :account_id, :user_id, :available_credit
belongs_to: user
I am not sure if I should:
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
更传统的方法是更多地使用复式记账法设计。
您可以在任何地方阅读有关此设计的信息，但这里有一个 漂亮的链接很好地讨论了它是什么以及它是如何工作的。
您的设计结果是您需要一个单独的表将示例事务的两个部分绑定到一个工作单元中。您需要一张表为每个业务交易提供一个条目，而另一张表为该交易中涉及的每个帐户提供一个条目。在引用的链接中，这些是 JOURNAL 和 POSTING 表。
在这种设计中，可用积分不会存储在帐户中，除非您想将其作为非规范化。通常，余额总是被计算的。
您可以决定拥有一个帐户余额历史记录表用于报告目的，但如果您的系统允许编辑旧交易（但事实并非如此，），您将需要确保重新计算此历史记录如果它是传统的会计系统）。
如果您想区分待处理的事务和已完成的事务，则可以在事务上放置状态标志（在引用的链接中的 JOURNAL 表中）。然而，还有一种更成熟的会计惯例，它会给每个用户两个帐户，一个是赚取的积分，一个是非赚取的积分。在此方案中，使用您的示例，B 和 C 会将一些积分存入 A 的待处理信用帐户。这将立即从 B 和 C 的账户中提取资金，这样他们就不会将这些积分花在其他地方。当 A 完成作业时，这些积分将从 A 的待处理帐户转移到 A 的当前帐户。如果工作被取消，您需要进行一笔交易，将 A 的待处理账户转回 B 和 C 的活期账户。
这种方式可能看起来有点复杂，但它具有明显的优点，即始终记录发生的所有事情。
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.