danago Posted July 1, 2009 Share Posted July 1, 2009 Hi. I am fairly new to mysql, and just had a question about organizing some information. What i wanted to do was store information about approximately 15000 objects. There are 5 pieces of information i need to store for each object. Where the problem comes in is updating the table. Of those 5 pieces of information, 3 of them change daily, so i need to somehow add those into the database, without replacing the old values. I basically need to keep a historical record of this information. Does that make sense? Im not even sure if this is viable, so any input is appreciated Thanks in advance, Dan. Quote Link to comment Share on other sites More sharing options...
gassaz Posted July 1, 2009 Share Posted July 1, 2009 how do you think store the data?? please show the db design that you have made until now.... Quote Link to comment Share on other sites More sharing options...
danago Posted July 1, 2009 Author Share Posted July 1, 2009 how do you think store the data?? please show the db design that you have made until now.... I dont really have a solid design, but i thought about perhaps creating a separate table for each of these objects, and then a separate row in each table for each updated piece of information. I guess my biggest concern with this is that i dont know if this will be efficient, or even possible? Things have changed a little and i will no longer have 15000 objects to collate information on; the number has been cut down to less than 1000 (not sure on precise number yet). Is it at all viable to have a database with 1000 tables? Sorry if i have missed out on some vital information that you need to assess the situation. Like i said, i am fairly inexperienced with mysql, and this is more of a personal project so i didn't really want to hire someone to do it professionally. Thanks for the reply. Dan. Quote Link to comment Share on other sites More sharing options...
gassaz Posted July 1, 2009 Share Posted July 1, 2009 First.. make a list of things that you want to store.. example: object name object description price date of buy try to make the list and post it... Quote Link to comment Share on other sites More sharing options...
danago Posted July 1, 2009 Author Share Posted July 1, 2009 Sure: Object Name Object ID Date Minimum Price Market Price Maximum Price Thats pretty much it on the information that i need to store. The price information updates approximately once per day. Quote Link to comment Share on other sites More sharing options...
gassaz Posted July 1, 2009 Share Posted July 1, 2009 well that is your table... Now, what things do you want to make a history? Quote Link to comment Share on other sites More sharing options...
danago Posted July 2, 2009 Author Share Posted July 2, 2009 well that is your table... Now, what things do you want to make a history? Basically, the max, min and market prices will change everyday. The item name and ID will remain the same. So it basically means adding one new row into each table each day, rather than just updating the rows, because id like to keep a histoy of the price data. So are you saying it is viable to have 500+ tables in a single database? Because that would pretty much solve this for me Thanks for your replies, Dan. Quote Link to comment Share on other sites More sharing options...
fenway Posted July 2, 2009 Share Posted July 2, 2009 I still don't see why 500+ tables would be required. Quote Link to comment Share on other sites More sharing options...
danago Posted July 2, 2009 Author Share Posted July 2, 2009 I still don't see why 500+ tables would be required. One for each object that i need to keep data on. Is there a better way to do it? Quote Link to comment Share on other sites More sharing options...
BMurtagh Posted July 2, 2009 Share Posted July 2, 2009 The best way to do this would be to read up on data normalization for MySQL database design (It sounds worse than it is) but this will get you thinking in a more programming/design-orientated state of mind. The reason for this is because you want to keep a record of past pricing. The way I'm picturing your table design is: tblOne - Object Name - Object ID (PK) - Date - Daily Minimum Price - Daily Market Price - Daily Maximum Price tblTwo - ID (PK of tblOne) -- should pull the data from tblOne w/ the min, market, max & date Once you have the data in there you can just increment the rows at the end of each day from the data in tblOne. This just my 2 cents, but below are some URLs to info about data normalization: http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/ http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html http://www.developer.com/db/article.php/3667831 Quote Link to comment Share on other sites More sharing options...
danago Posted July 2, 2009 Author Share Posted July 2, 2009 Thanks very much for those links! I read through them all and found some very handy information. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.