I am updating my website whereby I wish to add the ability to rate and comment on the products / items. I have never dealt with any SQL database structure of this complexity so any help would be appreciated. My overall focus on efficiency and ease of design and expand-ability obviously.
Firstly, each item has roughly 30 descriptive attributes, so:
- is it OK to store all of these in a single database with product ID or auto-incremented PK? (there may be more than 1000 products)
- this information will be displayed over several tabs grouping like data, should I use one query to pull all the information at once form the db (then simply hiding / unhiding relevant data using CSS) or an AJAX call for each tab.
- In terms of structure should it be something like:
1. users table
2. products table
3. comments table
4. ratings table
or should there be one db per product? or is there a more efficient way of storing this data.
- A single database is fine for this. Even with 2000 products and 200 comments/ratings per product, you're looking at 400k records in your largest tables. That's more than reasonable, as long as you look into indexes as things slow down.
- That's really a judgement call. If you expect that most users will click enough tabs to see most of the data anyway, you might as well pull it all down at once to save on the number of queries. If not, the AJAX idea would probably be more efficient by reducing data returned by the database. However, it's unlikely that the difference would be significant in either case unless you're serving tons of users.