pepsi_max2k Posted April 21, 2009 Share Posted April 21, 2009 Hey all, I was just advised to use MySQL as a database for the following info, but I'm new to it and I'm not sure how I can use it to store multiple entries for a field in a single record. I'm trying to keep a record of prices of some items, and want to include for each item: EAN (like UPC), maybe ID (could use EAN for it), product name, genre, maybe a few other things Date (probably weekly), price (for that week). date, price date, price etc etc. So for each product, I'll be continually adding new dates and prices, and need to keep the previous date and price data stored, but I can't find any info on how to do that as all the basic examples I've found just include a single entry for each field, and I know I shouldn't be continually adding new fields. And using a new table just to store date and price data for each product sounds crazy (I'd have 1000s of tables). Any tips? Thanks EDIT: 1 database for products, then another for dates + prices, which for each entry includes a link to the product (by ID / EAN)? That's gonna be a lot of unrelated data in one table... I could create multiple ones for each genre of product, but still a lot of data in each table. Link to comment https://forums.phpfreaks.com/topic/155007-how-to-store-historical-data/ Share on other sites More sharing options...
Mchl Posted April 21, 2009 Share Posted April 21, 2009 And using a new table just to store date and price data for each product sounds crazy (I'd have 1000s of tables). Any tips? Thanks. Two (2) tables will be enough for that. One to store some 'constant' data. Second to store price + date. Link to comment https://forums.phpfreaks.com/topic/155007-how-to-store-historical-data/#findComment-815279 Share on other sites More sharing options...
pepsi_max2k Posted April 21, 2009 Author Share Posted April 21, 2009 Is that gonna be ok for say around 10,000 products, and maybe date and price info every day, for over say 10 years? that's... erm.... around 36,500,000 entries for date and price in one table? And if it went to 50 years and 100,000 producs.... That's not gonna slow things down, or cause any redundancy issues? Link to comment https://forums.phpfreaks.com/topic/155007-how-to-store-historical-data/#findComment-815290 Share on other sites More sharing options...
Mchl Posted April 21, 2009 Share Posted April 21, 2009 No more than having separate table for each product (each table means another file on the disk that has to be opened, loaded into memory, scanned, etc.). Databases work well on 'long' and 'thin' datasets. And redundancy? That's exactly how you avoid it Link to comment https://forums.phpfreaks.com/topic/155007-how-to-store-historical-data/#findComment-815307 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.