How to design tables to take into account changes in prices for services. There are two tables:

mysql> SHOW COLUMNS FROM services; +---------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+----------------+ | service_id | int(11) | NO | PRI | NULL | auto_increment | | lesson_id | int(11) | NO | MUL | NULL | | | service_end | datetime | NO | | NULL | | | service_start | datetime | NO | | NULL | | +---------------+----------+------+-----+---------+----------------+ mysql> SHOW COLUMNS FROM lessons; +--------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+----------------+ | lesson_id | int(11) | NO | PRI | NULL | auto_increment | | lesson_name | varchar(60) | NO | UNI | NULL | | | lesson_worth | decimal(20,4) | NO | | NULL | | +--------------+---------------+------+-----+---------+----------------+

It is necessary that the prices in the services table be kept until they are changed, if any, that is, price changes for lessons did not affect the available lines in services .
Found a solution with a table that stores the history of price changes (if there is a better offer), then what price should be stored in services : from the history table or from lessons ?

UPD :

Came to this model, based on the answer.

 mysql> SHOW COLUMNS FROM services; +---------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+----------------+ | service_id | int(11) | NO | PRI | NULL | auto_increment | | lesson_id | int(11) | NO | MUL | NULL | | | service_end | datetime | NO | | NULL | | | service_start | datetime | NO | | NULL | | +---------------+----------+------+-----+---------+----------------+ mysql> SHOW COLUMNS FROM lessons; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | lesson_id | int(11) | NO | PRI | NULL | auto_increment | | lesson_name | varchar(60) | NO | UNI | NULL | | +-------------+-------------+------+-----+---------+----------------+ mysql> SHOW COLUMNS FROM worths; +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | worth_id | int(11) | NO | PRI | NULL | | | lesson_id | int(11) | NO | MUL | NULL | | | worth | decimal(20,4) | NO | | NULL | | | worth_date | datetime | NO | | NULL | | +------------+---------------+------+-----+---------+-------+
  • For a start, it would be nice to describe the business logic. What are services and what are lessons, how are their prices related to each other? - German Borisov
  • @HermanBorisov, services is the ultimate service that stores an occupation, its beginning, and its end. lessons - classes, name and price. - Pakhan
  • I join the commentary above. It is not clear what this is for and what should be in the end. - Alexxosipov
  • To option can be done in one table. For each line, make the start date of the action and the end date, when updating the record, update the old record by putting the expiration date on it and insert a new one with the current start date of the action. - heff
  • Please describe: when buying a lesson, can it be extended monthly? If so, then one month will cost n (and the price for it should be n during this month), and the next month should already cost m? What is the essence of this? - Alexxosipov

1 answer 1

Found a solution with a table that stores the history of price changes (if there is a better offer)

In my practice, a table with a history of change is the best solution.

what price to store in services: from history table or from lessons?

From the point of view of relational algebra, if something can be obtained from some tables, then you should not add it to others. Those. Ideally, the price should be kept only in the price history table.

From the point of view of performance, it makes sense to keep the price that is read more often than others (for example, current for today, or the price at the date of opening the service) in the main table. But it only makes sense to do this if you are sure that this single join with the history table seriously affects performance.

Amendment. If an individual discount can be provided for a client, then the price field in services is mandatory.

  • About the amendment: that is, the скидка field and the конСчная ΡΡ‚ΠΎΠΈΠΌΠΎΡΡ‚ΡŒ , which is calculated on the basis of the скидка and Ρ†Π΅Π½Π° arithmetic of the fields? - Pakhan
  • one
    @Pakhan, depends on business logic. If the negotiations are conducted individually with each client, then the final price may not be at all related to the list price. In this case, only the Ρ†Π΅Π½Π° field in the services table is valid. If the discount is chosen by the manager / seller from a fixed set, then the discounts should be recorded in a separate table, and in the services write the discount id and the calculated price, rounded off to kopecks. This is necessary to ensure that the amount received in one request does not diverge from the amount of all the lines counted by the accountant. - German Borisov