How to save complex data?

Level 80

@ap1234 You should save the data like you would a “normal” order: by saving the price (and any information such as product name) at the time, i.e. when you create the quote. But then you need to decide what happens if:

You create a quote containing Product A, that is $10.00 at that time.
Product A’s price is updated to be $15.00 after the quote has been created.
Customer now wants to proceed: do you charge them $10.00 as quoted? Or the product’s new price of $15.00?

This, unfortunately, is business logic and is something you will need to decide and cannot be answered by any of us for you.

Level 1

@martinbean Thanks for your reply! I get your point. Let's say you have to save everything as it as that time, so how do I save it then? Do you suggest to save everything? Make the same tables for what it is in products for ordered products?

A product can be deleted permanently. So can't save the id.
A product can be updated, anything could get changed here any relation with the product. Again can't save the id.

There is no price included but the values should be the same as it is when a quote or a order is created. A product has many relations then how to save them?

jlrdw

Level 75

@ap1234

A product has many relations then how to save them?

An orders table. Each invoice has to be saved separately. Meaning a separate table for this.

I highly suggest some basic business courses for a better understanding.

Level 80

@ap1234 It really depends on what you’re actually building a quote on. Usually just saving the name and price is enough.

If the product can be deleted, then you should have a nullable foreign key in your quotes-related tables. That way, if a product is deleted, you can set the foreign key column’s value to null, and then in your application you can check for this and show “Deleted Product” or something similar when viewing the quote.

Level 1

@jlrdw Hey! I do have a orders table and order products table. But my question was do i have to make the relational tables for hasMany relation in product_fields table as well? so order_sizes and order_colors? What about customer? Customer can be deleted as well, I need all the data from customers table, so make a different table or add all columns in orders table?

Level 1

@martinbean I can't do that, once the quote is sent to the customer there are no take backs. Even if the product gets deleted from the products table I have to save all the data some place else. I was looking for some better way to do it, instead of making the same database structure for orders like it is for product. But thanks for your answer!

Level 80

@ap1234 Can’t do what?

You should save the product name and price at the time the quote is created. That way if the product is deleted in between the quote being created and the quote being converted to an order, you will be able to show that the product has since been deleted, but what the name and price was in a line item on the quote.

If a product shouldn’t be deleted if it belongs to open quotes, then again that’s business logic and you need to actually handle that scenario in your application and prevent deletions.

1 like

Level 1

@martinbean Thanks for your answer! I will figure out something.

jlrdw

Level 75

@ap1234 When you go to a hardware store and buy a shovel and a bolt, that receipt (transaction / invoice) is stored in another table, i.e., an invoice table.

If you need to return it, the invoice is pulled up, not the product.

It is a separate related table of anything bought by you that day.

So you have (just example:

Invoice 12578444178

----- Shovel
----- Bolt

This invoice is stored and has nothing to do with your products table at all. It's separate.

Of course the invoice has price, date, etc.

Say the hardware store changes the price on the shovel, this has nothing to do with an existing invoice. It is a separate thing.

Again, I suggest some basic business and bookkeeping courses so you will understand how to do this stuff.