How to update the database in the most efficient way?

Question

I am creating a price comparison site that contains about 300,000 products and several hundred customers.

Every day the site needs to update prices and availability of stock suppliers.

When a supplier needs updating, I thought about deleting all the information about the seller, and then pulled and inserted a new one - every time.

By doing this, I don’t need to worry about the supplier uninstalling the product. In a simple way, I got a new dataset every day.

On the other hand, I need to keep the auto-increment counter in check, and it seems like spending everything on removing everything from the seller if he has only updated prices for 3 products in his entire warehouse.

But the update has its drawbacks. I still have to read all the data and compare each price and availability of the product with the supplier’s data, one product at a time. It is also more difficult to notice products that are being removed by the supplier.

How to achieve the most effective and simple update?

Answer 1

I am not sure which database technology you are using. In any case, the database supports processing relationships between multiple tables. It is up to you to implement the concepts. For example, you can use the CASCADE parameter when creating table A that references table B (for example, primary keys) to ensure that updating / deleting tuples in table B cascades (reflects) the corresponding tuple in table A.

See CASCADING example and when to use