Should these be 3 SQL tables or one?

Question

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(?).

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.

• Transactions are between users, like mutual credit. So units get swapped between users.
• Inventarizations are physical stuff brought into the system; a user gets units for this.
• Consumations are physical stuff consumed; a user has to pay units for this.

|--------------------------------------------------------------------------|
|  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).

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).

Answer 1

You said that amount will be different units, then i think you should keep each table for itself.

I personally hate a DB design that has "different rules" for filling a table based on the type of entity that stored in a row. It just gets messy, and its hard to keep your constraints alive propperly on a table like that.

Just create an indexed view that will answer your balance questions to keep your queries "simple"

Answer 2

There's no definitive answer to this and answers will largely be down to the database design methodologies adopted by the answerer.

My advice would be to trial both ways and see which one has the best compromise between querying, performance and maintenance/usability.

You could always set up a view that returns all 3 tables as one table for querying and has a type field for the type of process a row relates to.

Answer 3

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.

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.

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.