How to I setup a database that tracks prices for 100k+ products over time?

Question

I would like to setup a server that stores prices data for potentially 100,000+ products over time. Updates will be provided once or twice per month.

I would also have many components of such prices. Such that I run out of the 255 fields that Access allows me, and would burst the 2 Gig limit sooner or later. (New fields might just pop up at any moment for some products)

The scale of this project is somehow too small to get database experts to do a full scale database at the moment. Is there any quick fix I can do with the free Microsoft SQL Server ?

Or I am going to run into hardware limitations also?

Answer 1

You need to be more specific about what you want. If you are using 255 fields, then your table is broken.

But, to answer your question, something like the Express edition(s) of SQL Server will have no problem at all handling 100,000 products (or millions of products, for that matter, assuming your hardware is decent).

Answer 2

PostgreSQL is an open source dbms that doesn't have size limitations like the free versions of SQL Server and Oracle do. All dbms are naturally constrained by available disk space and available swap space. (But they're not all constrained the same way.) Some free dbms are constrained to use a single CPU; I'm pretty sure SQL Server Express is one of them.

And most dbms have a row size limitation. Depending on the dbms, the row size limitation might be a hard limit (can't create a table that stores more than 'n' bytes per row) or a soft limit ("long" rows--or at least parts of them--are moved to a different part of the database, which affects performance).

You can google "row size limitation", or search SO for the same phrase.

It would be interesting to see the structure and functional dependencies for a table of prices that needed 255 columns.