Table structure issue

[flydev]

Hello, I apoligize for the vague subject, but it's hard to wrap up in a single sentence what it is I am trying to do.

I am creating the ability for users on my site to purchase (with virtual dollars) different items, after the item is purchased, it will be available to them for use on the site. There is no set number of items, as they will be added continuously. I need to ability to track which users have which items purchased. Right now I am just keeping a single table with all purchases from all users, and the "item ID" is used to track which item was purchased. But over time this list is getting into the tens of thousands and seems like it could be reducing performance to have to filter through every entry for a single users purchased items on every page click.

Is there a better method for this, or am I just being paranoid?

[coolsti]

Your post is 3 days old so maybe you are not looking for an answer anymore.

Try to post an example table structure or two, and we can give you better advice.

Basically, databases are quite good at handling large tables with lots of information. If a particular table gets large and queries become slow, you can speed things up (for the select queries at least) by adding an index or two.

Where you may wish to split into two tables is if you have a lot of redundant information being carried along in your one main table. Like if a user has the information "userID", "firstname" , "lastname", "address" etc., then there is no reason to have all of these attributes repeated on every row in a table that lists the purchases. Then you would use "normalizat ion" to break the tables down into more tables, where for example one table for the purchase info would only contain the "userID" as a foreign key to another table describing each user.