Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!
  • Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • You can also use markdown to share your code. When using markdown your code will be automatically converted to BBCode. For help with markdown check out the markdown guide.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

Do we need to use table relations when storing invoices and other documents?

Deja Vu

New Coder
Hi, my name is Dejan, I work as amateur programmer for 20 years.
Mostly I work on point of sale apps and bookkeeping apps.
Today I am mainly using Spring and Angular programming frameworks.
My apps up to now were succesfully created without using sql table relations at all, and they are all working without hickups after all these years.

I am trying to implement database relations in my sql desing but some things confuses me for years and keeps me from using relations in my apps desing.
I am interested how to store documents in tables, particullary invoices.
In all my apps I store full data in my invoice tables, meaning in my products columns i am storing full product name and price, in customer columns full name address of customers, practically duplicating data from customers and products tables and storing it in invoice tables, because i am not using relations.

But If I use relations to connect my invoice table with customers and products tables to avoid duplication of data I cannot allow to user to change product price or customer address because it will affect/change data in my all invoices that are connected by id of that product or customer.
But changing product prices or names or customers name/addresses are daily tasks of users.

So my conclusion is NOT to use relations in tables when storing documents because they need to be saved with exact data at the time when they were created.

If this is the case for storing documents in tables, then when to use relations at all?
 
Last edited:
Hi, my name is Dejan, I work as amateur programmer for 20 years.
Mostly I work on point of sale apps and bookkeeping apps.
Today I am mainly using Spring and Angular programming frameworks.
My apps up to now were succesfully created without using sql table relations at all, and they are all working without hickups after all these years.

I am trying to implement database relations in my sql desing but some things confuses me for years and keeps me from using relations in my apps desing.
I am interested how to store documents in tables, particullary invoices.
In all my apps I store full data in my invoice tables, meaning in my products columns i am storing full product name and price, in customer columns full name address of customers, practically duplicating data from customers and products tables and storing it in invoice tables, because i am not using relations.

But If I use relations to connect my invoice table with customers and products tables to avoid duplication of data I cannot allow to user to change product price or customer address because it will affect/change data in my all invoices that are connected by id of that product or customer.
But changing product prices or names or customers name/addresses are daily tasks of users.

So my conclusion is NOT to use relations in tables when storing documents because they need to be saved with exact data at the time when they were created.

If this is the case for storing documents in tables, then when to use relations at all?
HI there,
The easiest way to transform nosql document database into sql/mysql databases is to remember the following:
  • A nosql document would be the same thing as a row, or a record, in a sql/mysql database.
  • Every nosql document key would be a column in a sql/mysql table
  • The number of tables would mainly depend on how you choose to group the data
 
Hi, my name is Dejan, I work as amateur programmer for 20 years.
Mostly I work on point of sale apps and bookkeeping apps.
Today I am mainly using Spring and Angular programming frameworks.
My apps up to now were succesfully created without using sql table relations at all, and they are all working without hickups after all these years.

I am trying to implement database relations in my sql desing but some things confuses me for years and keeps me from using relations in my apps desing.
I am interested how to store documents in tables, particullary invoices.
In all my apps I store full data in my invoice tables, meaning in my products columns i am storing full product name and price, in customer columns full name address of customers, practically duplicating data from customers and products tables and storing it in invoice tables, because i am not using relations.

But If I use relations to connect my invoice table with customers and products tables to avoid duplication of data I cannot allow to user to change product price or customer address because it will affect/change data in my all invoices that are connected by id of that product or customer.
But changing product prices or names or customers name/addresses are daily tasks of users.

So my conclusion is NOT to use relations in tables when storing documents because they need to be saved with exact data at the time when they were created.

If this is the case for storing documents in tables, then when to use relations at all?
Regarding this:
Code:
But If I use relations to connect my invoice table with customers and products tables to avoid duplication of data I cannot allow to user to change product price or customer address because it will affect/change data in my all invoices that are connected by id of that product or customer.
But changing product prices or names or customers name/addresses are daily tasks of users.

So my conclusion is NOT to use relations in tables when storing documents because they need to be saved with exact data at the time when they were created.

If this is the case for storing documents in tables, then when to use relations at all?

What you can do is create tables to keep track of changes. Creating a table, like the schema below:
SQL:
create table CustomerChangeLog {
int ID unique pk,
int CustomerID fk,
date TimeOfChange,
varchar DataChanged,
varchar OldValue,
varchar NewValue
}

create table ProductChangeLog {
int ID unique pk,
int ProductID fk,
date TimeOfChange,
varchar DataChanged,
varchar OldValue,
varchar NewValue
}

Here, CustomerChangeLog keeps track of any changes that were done to a customer's record, and ProductChangeLog keeps track of changes done to a product. Having something like ProductChangeLog table should help remove the worry about having to change every customer record when a product data is changed.
 
Regarding this:
Code:
But If I use relations to connect my invoice table with customers and products tables to avoid duplication of data I cannot allow to user to change product price or customer address because it will affect/change data in my all invoices that are connected by id of that product or customer.
But changing product prices or names or customers name/addresses are daily tasks of users.

So my conclusion is NOT to use relations in tables when storing documents because they need to be saved with exact data at the time when they were created.

If this is the case for storing documents in tables, then when to use relations at all?

What you can do is create tables to keep track of changes. Creating a table, like the schema below:
SQL:
create table CustomerChangeLog {
int ID unique pk,
int CustomerID fk,
date TimeOfChange,
varchar DataChanged,
varchar OldValue,
varchar NewValue
}

create table ProductChangeLog {
int ID unique pk,
int ProductID fk,
date TimeOfChange,
varchar DataChanged,
varchar OldValue,
varchar NewValue
}

Here, CustomerChangeLog keeps track of any changes that were done to a customer's record, and ProductChangeLog keeps track of changes done to a product. Having something like ProductChangeLog table should help remove the worry about having to change every customer record when a product data is changed.
Thank you for replying.
This looks logical.
But, if I need to print a year report of all invoices of one customer,
then I will need to check all changes for all products and all customers in that period
which will dramatically slow down generating my report.
This looks very slow and very intense resource task,
I will rather use extra space duplicating data for documents than doing that operations for every one product and customer.
 
Thank you for replying.
This looks logical.
But, if I need to print a year report of all invoices of one customer,
then I will need to check all changes for all products and all customers in that period
which will dramatically slow down generating my report.
This looks very slow and very intense resource task,
I will rather use extra space duplicating data for documents than doing that operations for every one product and customer.
Unfortunately, that will make your operation even slower, because now you have to deal with duplicate data. Also regarding performance, an optimized query should significantly improve the performance. Ways to optimize a query include: only retrieving data you actually need, the less nested queries the better. Here's a more detailed list of ways to optimize sql/mysql queries
 
I just found answer to my dillemas on stack.
Here is the answer:
I know that it looks like you are denormalizing price by keeping it on your transaction table, and that denormalizing feels "bad" because we like to follow best practices. However, a better way to think about your problem here is that the price is not being denormalized in this case.
The price on your product table (or in a product history table) is semantically different from the price in your transaction table.
One is the MSRP (i.e. the "ought to be" price) and one is the paid ("actual") price. Yes, these will be the same most of the time, but that is coincidental.
You should keep the price actually paid in the transaction table, whether or not you keep the price in a history table. The reason for this is that the transaction table is a record of what actually happened. In a way it's a kind of a write-once log. Auditors will like it better if you can show that prices actually paid can't be restated later based on how your code works. In a traditional accounting system even corrections are applied using reversing transactions rather than edits.
Another thing to consider is that prices can have exceptions. What if you decide to have a promotion with coupons, for example? Or if you provide a 20% discount for "open box" items? These kind of one-off prices are difficult to track in a price history table.
For these reasons keeping the price actually paid in the transaction table is a valid design decision, not just an expediency for performance or code simplicity.

In short: storing full data in tables which stores documents is mandantory, and it is not treated as database denormalisation or bad practice. Because prices are prone to change and different customer can get different price due to coupons or discounts, so tracking prices and other data with changelog tables will be areally complex solutions in combination with table relations.

So I was doing it the right way all along (Im so smart 😉)
Thanks Antero360 for help.
 
Last edited:

New Threads

Buy us a coffee!

Back
Top Bottom