Infinite database

[absenm]

Before anyone flames me, I want to be clear, I have searched and researched this issue for years.  Maybe its my keywords or maybe its just my understanding, but nothing I have found seems satisfactory or even addresses my specific need.  Anyway, my gratitude in advance for everyone being polite and patient.

 

I have a website that keeps track of pricing data for an online gaming site.  When I made it I had no understanding of coding beyond BASIC on a Color Computer II from 1981.  So I put together everything bit by bit.  Now its three years later and I have a much much greater understanding of things.  But I'm stuck with a data base that contains 365 columns just for a year of pricing data on an item.  And I want to keep all data for every new day, not just one year. 

 

I know there has got to be a better way then name,bla,bla,day1,day2,day3,day4,day5... and adding columns for every new day.

 

Any suggestions?  And I know this question is mostly focused on MySQL (actually any database but I use MySql) rather than PHP.  But PHP will be playing an integral part of maintaining the database.  Plus, the people here are always incredibly knowledgeable, and polite.

 

Again, the specific question is how should I design a database that will keep an on-going history of pricing data?

[scootstah]

You haven't posted any sample data so I have no way of knowing what kind of information you are actually storing.

 

With that in mind, can you not just use a DATE field?

[UrbanDweller]

Why not use arrays so stack up data then insert into db?

[Muddy_Funster]

Date field identifier would be my method of choice

[PFMaBiSmAd]

Each piece of data belongs in a separate row in your database table, with identifying information, such as the full date it corresponds with, not spread out in columns.

[KevinM1]

I have a feeling about where this may be going.

 

The key to a database like MySQL is that it's a relational database.  Trying to use it like a gigantic spreadsheet is exactly the wrong way to go about it.  Instead, you need to create tables that model how the data is actually related.  This is done by a process called 'normalizing'.  A good introduction to database normalization can be found here: http://mikehillyer.com/articles/an-introduction-to-database-normalization/

 

You want to design your tables first, then add data to them.  Your tables should remain static during that process (so, no adding actual table columns like you alluded to above).

 

I hope this helps.

[absenm]

Thank you all for your prompt replies.  To help clarify...

 

Basically the data structure I currently have is

 

Item Name, Price Day1, Price Day2, Price Day3, ... Price Day 365

 

where each item has its own row in the table.  We track 3500 items.  As someone already mentioned, yes it is more of a spreadsheet style. Sample data would be

 

Dragon stone, 7832, 7833, 7900, ... 7950

 

My website determines the date of the data based on the column location, for example day90 would be three months ago.  Again I know this is terrible.  I didn't know what I was doing when I first designed the site.  Now I get three million hits a year and I really want to do things properly so I can grow as a developer and my site can grow within its community.  I am completely self taught.  And I probably learned more bad things then good.    Like using mySql like Excel.

 

If you want to see how the data is used try this page http://www.grandexchangecentral.com/item.php?rid=21369.  I hope posting that link doesn't violate the rules.  I did just read them again and I think I'm fine.

[scootstah]

You'll want to normalize that a bit. Instead of having 365 price columns in the items table, make a new table called "prices" (or whatever you like). In this table have columns: item_id, price, date.

 

This way you'll have one row per price, with a date linked to each item. It would be very easy to select data in whatever increments you like (daily, weekly, yearly, etc).

[absenm]

KevenM1 - I am beginning to read that article.  There is a lot to wrap my head around so I will comment later about it and whether it was helpful to my issue.

 

scootstah - If I am understanding your suggestion properly, every day would increase the table size by 3500 rows.  Just a years worth of data would have 1.27 million rows.  That seems scary large.  Or am I looking at this the wrong way.

[Muddy_Funster]

...  Just a years worth of data would have 1.27 million rows.  That seems scary large.  Or am I looking at this the wrong way.

Kind of.  It does sounds large if your thinking about it in human terms, like sheets of paper, or post-it notes sticking on your desk.  For a databse, as long as there is sufficient disk space and it's set up right at the start, it's actualy really small (how many entries do you think the google DB has in a given table?).  And before long term concerns creep in, you can always archive the records off into a series of aged tables every day/month/year/decade depending on what's easiest for your requirement.

[absenm]

Wow, thank you all. 

 

KevenM1 - That article was great and gave me a much better understanding and what I need to do and what I have been doing wrong.

 

Muddy_Funster & scootstah - Yes, I have been thinking about this all the wrong way. Further research has also pointed out to me that a couple million rows (or records) is not much really.  As long as I do it properly and make sure I got the right indexes and keys.  And in retrospect, those millions of datapoints are still in my current setup, just spread-out through the columns.

 

Thank you all.

[rythemton]

You could also save yourself some rows of data by only adding rows when the price changes, rather than updating every day. Properly created queries would still pull the same data.

$query = 'SELECT * FROM price_changed WHERE id=25 AND date<="2012-03-22" ORDER BY date DESC LIMIT 1';

That would give you the price on March 22nd, even if the last time the price changed was in January, without having to put a new price in every day.

[scootstah]

scootstah - If I am understanding your suggestion properly, every day would increase the table size by 3500 rows.  Just a years worth of data would have 1.27 million rows.  That seems scary large.  Or am I looking at this the wrong way.

 

It may seem "scary large", but for a database it's really not. That's exactly what an RDBMS is built for, and they are very good at it.

 

You can always truncate your records or, like Muddy_Funster said you can archive them.

 

Just as a point of reference, this board currently has 1,320,797 posts... so that's 1,320,797 rows just for posts, which hold a lot more information than what you need.