What is the best way to store a historical price list in a MySQL table?

Question

Basically, my question is that I have a list of prices, some of which are historical (i.e. I want to be able to search that product X was $ 0.99 on March 11, $ 1.99 April 1, etc.) What is the best way to store this information?

I suggested that I would probably have a product table that has a foreign key to the price table. Initially, I thought that maintaining the current price would probably be the best bid, but I think I want to be able to store historical price data, so there is a better way to store a table, such as the following for the price list:

CREATE TABLE prices (
         id BIGINT auto_increment not null,
         primary key (id),
         price DECIMAL(4,2) not null,
         effectiveStartDate DATETIME NOT NULL,
         effectiveEndDate DATETIME 
);

I'm losing a little here. I would like to be able to efficiently search for products and see how the price of a product has changed over time. How can I effectively associate a set of these prices with a product? I suppose I ask: "What would be the best way to index this in order to be able to efficiently search for queries that span a specific set of dates?"

Answer 1

Separate the need for historical data from the need for the current price. It means:

1) Save the current price in the product table.

2) , . , . ( , )

, , .

Answer 2

-, , . ? , , . ; , , - .

, , , , . , , ; , , .

Answer 3

, (.. / ..) . (, BS, ... , , , - ... , /)

, "" (.. - , - ..)

/// , "id" [product_id, start_date,...?..] pk. ( , ), , , .