Should they be 3 SQL tables or one?
This is a new question arising from this question
Because of the answers, the nature of the question has 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 records for a specific user for the run_balances calculations.
- User-to-user transactions such as mutual loans. Thus, devices are swapped between users.
- An inventory is physical material entered into the system; the user gets units for this.
- Consumption is a consumable physical material; the user has to pay for it.
| ------------------------------------------------- ------------------------- | | 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 "sum" is in different units, these are records and calculations done in those quantities. Outside the scope to explain why, but these are fields).
The question arises: "Could / be it in one table or be multiple tables (as I have at the moment)?" I like the 3 tables solution because it makes more semantically sense. But then I need such a complex selection operator (possibly negative) for run_balances. The original question in the link above was asking for this statement, here I am asking if the db design fits (sorry for four double posts, hope everything is ok).
source to share
The same question arises when you are trying to implement a single-entry ledger system. What you call "transactions" corresponds to "transfers", for example, from savings to verification. What you call "inventions" corresponds to "income" like a salary deposit. What you call "consumption" corresponds to "expenses", for example, when you pay an electric bill. The only difference is that in accounting, everything has been reduced to the value of the dollar (or other currency). Therefore, you don't have to worry about identifying assets because one dollar is as good as the other.
So the question is whether you need separate columns for "debit amount" and "credit amount" or, alternatively, can you just have one column for "amount" and enter a positive number for debit and negative amount for loans. Essentially, the same question arises if you are doing double-entry bookkeeping rather than in bookkeeping mode alone.
In terms of internal arithmetic and internal data processing, things are much easier if you take a single column approach. For example, to check if a given transaction is in the balance sheet, all you need to ask is if the amount (amount) is zero.
Difficulties arise when people demand the traditional accounting format for data entry forms, based on search screen and published reports. The traditional format requires two separate columns labeled "Debit" and "Credit" that contain only positive numbers or spaces, with the restriction that each item must have a debit or credit entry, but not both, and the other column must be empty. These conversions 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 keep the traditional accounting format side-by-side with debit and credit coupons, or is it better to move forward towards a format that uses negative numbers in a meaningful way? There are some circumstances that favor each of these design options.
In your case, this will depend on how you intend to use the data. I would build prototypes with each of the two constructs and then start working on the fundamental CRUD handling for each. Depending on which one is easier in your environment, you can choose.
source to share
You said that the number will be different units, then I think you should keep each table for yourself.
I personally hate the DB construct that has "different rules" for populating a table based on the type of object that is stored in the string. It just gets messy and its hard to keep your constraints alive on the scoreboard.
Just create an indexed view that answers your balance questions to keep your queries "simple"
source to share
There is no definitive answer to this question and the answers will mostly relate to the database design methodologies adopted by the respondent.
My advice would be to try both ways and see which one has the best compromise between requests, performance, and service / usability.
You can always customize a view that returns all 3 tables as one table for the query and has a field type
for the process type the row belongs to.
source to share