MySQL and PHP Database Design

Help!

[hdpt00]

I am starting to program a new site which will involve some very complicated PHP/MySQL. But my main concern, since I'm new to this, is the size of the database. This site will have "users," "products" etc.

Now lets say the users list goes into the hundreds of thousands, I need to record all the users and what each user purchased as well as other stuff. I also have a huge database of products.

Now should this all be put into one database with logical table breakups, such as a table for all the users, all the products and all the purchases? Or should it be broken up so that each user has a table and all that users’ activity is recorded there, but then there may be 100,000 tables.

Should I use different databases, one for products, one for users, etc.? How long should a table be at maximum before it becomes too slow? If I have a table with 2 million products will that be too slow to sort stuff? Should I make a table for each manufacturer that has, say, 100,000 products?

Thanks for any help!

[dcrombie]

1) keep everything in one database;
2) keep similar 'records' in a single table (ie. one for users, one for products, ...)

Anything else is a recipe for disaster.

If/when you encounter storage/speed problems, make sure that everything is optimised as much as possible (no data duplication, minimal database calls and size of data returned, indexes on tables, ...); look at whether you can 'pre-generate' static content for part/all of the site; make sure everything that can be 'cacheable' is; ...

THEN if there's still problems, consider upgrading your server/database.

Just my 2c ;)