Essentially, my real question is this - I've a listing of costs, most of which are historic (i.e. I wish to have the ability to search that product X was $.99 on March 11, $1.99 on April 1, etc...). What's the easiest method to store these details?
I believed I'd most likely possess a Product table which has a foreign answer to a cost table. I initially believed that storing the present cost would most likely function as the best choice, however i think I wish to have the ability to store historic cost data, so would the greater path to go be to keep a table such as the following for that cost 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 am at a little of the loss here. Let me have the ability to search items effectively and find out the way the cost of this product transformed with time. How do i effectively connect some these prices having a product? I you know what I'm asking is, 'What could be the easiest method to index this to be able to have the ability to offer an efficient look for queries that span a particular group of dates?'
Separate the requirement for historic data from the requirement for current cost. What this means is:
1) Keep your current cost within the items table.
2) Once the cost changes, place the brand new cost in to the history table with just the start date. You do not actually need the finish date since you can have it in the previous row. (You are able to still place it in, it can make querying simpler)
Keep in mind that the order history provides another type of history, the particular purchases in a given cost with time.
First, make certain that you simply really have to do this. Are you currently storing orders within the same database? If that's the case, you could view historic cost trends by analyzing the cost from the item in orders with time. This can also permit you to make correlations between cost changes and alterations in ordering designs the only real situation it can't address is that if a cost change led to no orders being placed.
That being stated, if you would like a completely independent record of cost changes, what you've presented is nice. The only real factor I would suggest is getting rid of the finish date unless of course you intend on getting a gap over time in which the product has no cost or overlapping prices, start date is enough and can build your logic simpler.
The finish date might be viable for additional complex system where one can plan prices of product (i.e. various periodic promotions/etc.) ahead. (oh, this really is Baloney, must have thought much more about it ... ok, you'll need finish date only when you intend multiple prices of product simultaneously, classified by another thing ... still it's frequently convenient to be inside current record, not searching at previous/next one)
Really with many complex systems it's not uncommon to possess several current prices classified by "dimensions" only (i.e. some type of attribute which might be then made the decision by actual shipping place or customer's country, etc...)
I'd also check two times your platform/language/framework/type of work before you decide to omit the custom "id" primary key in support of [product_id, beginning_date,..?..] composite pk. The second is sort of more logical choice (a minimum of Personally, i prefer it), however it may backfire sometimes, for instance in case your DB library only has limited way to utilize more complicated primary secrets.