sqlitesqlitestudio

SQLiteStudio automatically insert value

I have a database created with SQLiteStudio that has a products table with two columns, item and price. It also has a sales table with an item column that is linked to the item column in products. I'd like the sales table to also have a price column, whose value is automatically set to that of the products.price row corresponding to the value selected from the products.item column. How would I define the sales.price column so that this value is automatically set?

Also, the prices in the products table may be changed from time to time, but the price listed in any existing sales records must not be updated when this is done.

Solution

  • SQLite has generated columns (also called expression columns) that are found in other DBMSes. But you'll need a fairly recent version of SQLite:

    Generated column support was added with SQLite version 3.31.0 (2020-01-22). If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt.

    Source: Generated Columns

    You could simply build a view to augment your table a little bit.

    You have mentioned that the prices will change over time and this is perfectly normal. So you have at least two design choices:

    1. add an additional table to store price history, in that table you store product ID, price, start date and end date (as an example). Then you join it with the other tables. The effective price shall be determined based on the order date. That also means that the prices are to be stored in that table and not in products... you have to redesign your schema slightly.
    2. the other option is to store the unit price of the product in the sales table as a historical value. This is the price that was in force when the sale was made.

    One thing to consider: you may require more flexibility on pricing: it can depend on the client (different rates based on volume) and also on specific circumstances. The final price may be the result of numerous and complex calculations.