Need a DB option for archiving ecommerce data
-
Regardless of which type of DB you use, you could have the most recent information in a table... say... products.
The history / old information could be stored in products_history or something like that.
That way your most recent information is a simple query, and if you need to review a product's history, then you query the history tables?
-
What we use in finance is called a "tick" system. A column (this could be its own table in an RDBMS, or it could be stored in something like Redis) that stores changes over time. It would only need to record the product, datetime and price.
-
@scottalanmiller said in Need a DB option for archiving ecommerce data:
What we use in finance is called a "tick" system. A column (this could be its own table in an RDBMS, or it could be stored in something like Redis) that stores changes over time. It would only need to record the product, datetime and price.
The billing application that we wrote for a client works like that more or less.
User wants to change prices, they select the products, set the effective time of the price change, set the new price, and the application save that to the pricing table in the DB.
Prices generally change daily and are effective from 6pm to 5:59pm, but can change at anytime with new effective times.
When invoices are generated, it queries the time of purchase from the transaction and then compares to the pricing table to find out which price to bill.
-
@scottalanmiller said in Need a DB option for archiving ecommerce data:
What we use in finance is called a "tick" system. A column (this could be its own table in an RDBMS, or it could be stored in something like Redis) that stores changes over time. It would only need to record the product, datetime and price.
So would I store the entire product "row" but with the added time stamp? Or would this just be the product ID with the single field and date stamp? Essentially just 3 or 4 columns for every single historical change?
If I store the entire row with all it's columns, there would be a lot of columns of empty data. If I store just ONE field that has a change, then the table as a whole would increase a lot in row count.
Either way I would have to pull every record matching an ID and parse out the history of each field. Not sure which method would be most performant or efficient.
-
@guyinpv said in Need a DB option for archiving ecommerce data:
So would I store the entire product "row" but with the added time stamp? Or would this just be the product ID with the single field and date stamp? Essentially just 3 or 4 columns for every single historical change?
Store the product "row" but do not store the price with it. Store that in a separate column. Then do a lookup against the product ID and time to coordinate the two.
-
@guyinpv said in Need a DB option for archiving ecommerce data:
If I store the entire row with all it's columns, there would be a lot of columns of empty data. If I store just ONE field that has a change, then the table as a whole would increase a lot in row count.
Anytime that that will or might happen, it tells you that you should have another table, in the relational database world.
-
@guyinpv said in Need a DB option for archiving ecommerce data:
Either way I would have to pull every record matching an ID and parse out the history of each field. Not sure which method would be most performant or efficient.
Only one method is even considered valid
Never let nulls exist in a relational database. -
I still think something more like an object store makes more sense than relational.
product = { pid = 12345, description = { [20160403, "Some long description"], [20160513, "Some long description2"], [20160821, "Some long description3"], }, otherfield = { [20150414, "field value"], [20150414, "field value"], } ... }This way, the entire product is still one record, but all those dated changes are just in sub objects and arrays. Seems more logical somehow than relational. I guess I'll have to play around with both ideas.
-
Nothing illogical about the relational, in fact, it's a "relationship" and exactly the kind of thing that relational databases are designed for. And since it is ecommerce data, it fits very, very much into the wheelhouse of relational. I'd argue that you are actually demonstrating the very use case that is where relational actually makes sense, rather than one where it doesn't.
-
@scottalanmiller said in Need a DB option for archiving ecommerce data:
Nothing illogical about the relational, in fact, it's a "relationship" and exactly the kind of thing that relational databases are designed for. And since it is ecommerce data, it fits very, very much into the wheelhouse of relational. I'd argue that you are actually demonstrating the very use case that is where relational actually makes sense, rather than one where it doesn't.
Yes, this is very much a relational design.
