这些应该是3个SQL表还是1个？

本文介绍了这些应该是3个SQL表还是1个？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是来自此问题的新问题

This is a new question which arose out of this question

由于答案，问题的性质发生了变化，因此我认为发布新的问题是可以的（？）。

Due to answers, the nature of the question changed, so I think posting a new one is ok(?).

您可以在下面看到我的原始数据库设计。我有3个表，现在我需要一个查询来获取特定用户的所有记录，以便进行running_balances计算。

You can see my original DB design below. I have 3 tables, and now I need a query to get all the records for a specific user for running_balances calculations.

• 交易在用户之间进行，就像互信一样。这样，单位就可以在用户之间交换。
• 库存化是带入系统的物理物料。
• 消费是消耗的物理物品；用户必须为此支付单位。

|--------------------------------------------------------------------------| | type | transactions | inventarizations | consumations | |--------------------------------------------------------------------------| | columns | date | date | date | | | creditor(FK user) | creditor(FK user) | | | | debitor(FK user) | | debitor(FK user) | | | service(FK service)| | | | | | asset(FK asset) | asset(FK asset) | | | amount | amount | amount | | | | | price | |--------------------------------------------------------------------------|

（请注意，金额以不同的单位表示；这些是对这些金额的输入和计算。在解释范围之外为什么，但是这些是字段）。

(Note that 'amount' is in different units;these are the entries and calculations are made on those amounts. Outside the scope to explain why, but these are the fields).

问题是：可以/应该在一个表中还是多个表中（就目前而言）？ 我喜欢3表解决方案，因为它在语义上更有意义。但是随后，我需要running_balances这样复杂的select语句（可能会对性能产生负面影响）。上面链接中的原始问题要求此语句，在这里我要问数据库设计是否合适（抱歉，四次重复发布，希望可以）。

The question is: "Can/should this be in one table or be multiple tables (as I have it for now)?" I like the 3 tables solution because it makes semantically more sense. But then I need such a complicated select statement (with possibly negative performance results) for the running_balances. The original question in the link above asked for this statement, here I am asking if the db design is appropriate (apologies four double posting, hope it's ok).

推荐答案

当您尝试为单项簿记实现总帐系统时，也会出现相同的问题。您所谓的交易对应于转移，例如从储蓄到支票。您所谓的库存化对应于收入，例如存入工资。您所谓的消费对应于费用，就像您支付电费时一样。唯一的区别是在簿记中，所有内容都已减少为美元（或其他货币）价值。因此，您不必担心识别资产，因为一美元和另一美元一样好。

This same question arises when you try to implement a general ledger system for single entry bookkeeping. What you have called "transactions" corresponds to "transfers", like from savings to checking. What you have called "inventarizations" corresponds to "income", like depositing a paycheck. What you have called "consumations" corresponds to "expenses", like when you pay the electric bill. The only difference is that in bookkeeping, everything has been reduced to dollar (or other currency) value. So you don't have to worry about identifying assets, because one dollar is as good as another.

因此，出现了一个问题，您是否需要为借方金额和贷方金额分别设置一列，或者是否只需要为金额一栏，然后为借方输入一个正数，为贷方输入一个负数。如果您实施的是两次记账而不是单次记账，基本上会出现相同的问题。

So the question arises whether you need to have separate columns for "debit amount" and "credit amount" or alternatively, whether you can just have one column for "amount", and enter a positive number for debits and a negative amount for credits. Essentially the same question arises if you are implementing double entry bookkeeping rather than single entry bookkeeping.

就内部算术和内部数据处理而言，采用这种方法要简单得多。单列方法。例如，要测试给定交易是否平衡，您要做的所有事情就是求和（金额）是否等于零。

In terms of internal arithmetic and internal data handling, things are far simpler when you adopt the single column approach. For example, to test whether a given transaction is in balance, all you have to do ask whether sum (amount) is equal to zero.

当人们需要传统的bookeeper格式的数据输入表单，屏幕检索和已发布的报表时，就会出现复杂问题。传统格式需要两个单独的列，分别标记为借方和贷方，它们仅包含正数或空白，并限制每个项目都必须在借方或贷方中都具有输入项，但不能同时具有两者，而另一列必须是留空。这些转换需要在外部格式和内部格式之间进行一定数量的编程。

The complications arise when people require the traditional bookeeping format for data entry forms, on screen retrievals, and published reports. The traditional format requires two separate columns, marked "Debit" and "Credit", which contain only positive numbers or blank, with the constraint that every item must have an entry in either debit or credit but not both, and the other column must be left blank. These transformations require a certain amount of programming between the external format and the internal format.

这确实是一个选择问题。保留传统的并排借方和贷方账簿格式是更好的方法，还是前进到以有意义的方式使用负数的格式呢？在某些情况下，这些设计选择都很有利。

It's really a matter of choice. Is it better to retain the traditional bookkeeping format of side by side debit and credit coulmns, or is it better to move forward to a format that uses negative numbers in a meaningful way? There are some circumstances that favor each of these design choices.

在您的情况下，这取决于您打算如何使用数据。我将使用两种设计中的每一种来构建原型，然后开始针对每种设计进行基本的CRUD处理。选择哪种方法在您的环境中更容易解决。

In your case, it's going to depend on how you intend to use the data. I would build prototypes with each of the two designs, and then start working on the fundamental CRUD processing for each. Whichever one works out easier in your environment is the one to choose.