Given: product control system. There are supplies and there are sales. Necessary: ​​to ensure data integrity.

In more detail: there is a table sale_products. It has a product ID and a sales ID. There is a table of products, it has the name of the product. There are several questions, namely:

How more correct: create a column in the products table and add / decrease the value depending on the sale / delivery

How to make a delivery table? There is a product that will be rejected, i.e. it is also necessary to make a write-off of goods

Do I need to make different tables of goods written off and goods delivered? I wanted to do this: the table is one, it has a type column, where 1 is delivery, 2 is write-off

Thank you all in advance for your help!

  • do you know about transactions? - Mikhail Vaysman
  • @MikhailVaysman yes, just thinking about whether there is an alternative way and which one is better - Alexxosipov
  • transactions just serve to ensure integrity. why some other way? - Mikhail Vaysman
  • From the point of view of a relational database, in principle, there can be no column with a quantity of goods in the products table. The quantity should be obtained according to the table of movement of goods (yes, it will be easier to make one table in which there will be both receipt and write-off of goods) as the sum of all operations. When there is a question of performance, then only in the products can a column be added which can trigger the movement table trigger - Mike
  • @Mike thank you) and this was the speech. Tell me, how soon can there be any question about performance? 10 thousand records? 100? - Alexxosipov

0