One big table or separate tables to store product reviews of part types?

Question

I need to make 100 or so tables. I have tables called PartStatsXXX and the tables to be made will all be called PartReviewXXX (they pair up with each other in a 1:n relationship).

Is it efficient to create one big table to store all product (product and part being the same term from a business perspective) reviews? Someone mentioned making a relationship from PartStatsXXX to PartsReview (one large table) with the value of XXX as part of the primary key from PartStatsXXX.

XXX is the name of the part type (eg battery, wiring loom, etc). So this will be varchar. Should I make a composite key? The part type wouldn't change names (though some part names can have multiple names depending on culture), but it's not really a candidate ID. It was then mentioned I could get several views for what I need depending on the value of XXX.

I hope this makes sense. What would be the best approach?

Thanks

Answer 1

Multi-table PartStatsXXX is a bad idea: hard to code properly or with a framework, harder to maintain, nightmare to query...

Use two tables: PartStats and PartsReview, with approriate keys and indexes for performance.

Answer 2

It is more efficient to create tables based on what you want to store in each one. You do not need 100 tables for 100 products. you need 1 table for all products.

So for your needs I would create 2 tables:

products
========
id INT
name VARCHAR 

product_reviews
===============
id INT
product_id INT (foreign key to products.id)
rating INT (example column)

Answer 3

Unless you are storing different types of data for each product's reviews (i.e., each table has a different set of columns), using a different table per product will be creating an unnecessary nightmare.

As a general rule, you never want to have more than one table with the same set of columns. As already suggested, one table with a "product_id" column is the way to go.

Answer 4

If you want to save yourself some pain in a quick-and-dirty way, use two tables.

CREATE TABLE PartStats (
  ...,
  PartType VARCHAR(255),
  ...
);

CreateTable PartReview (
  ...
  PartType VARCHAR(255),
  ...
);

and then join them up via

SELECT ...
FROM PartStats ps JOIN PartReview pr
  ON ps.PartType = pr.PartType;

This gets you out from having hundreds of tables, but sets you up for a different problem: Redundant data (PartType) that can get out of sync. A typo in a PartType can yield an orphaned review.

The solution here, assuming that you can have more than one PartStats entry for a given PartType, is to add a third table to the sole older of PartType names.

CREATE TABLE PartType (
  ID INT ...,
  PartType VARCHAR(255),
  PRIMARY KEY (ID)
);

and arrange for PartStats and PartReview to use the ID of a PartType. For example,

CREATE TABLE PartStats (
  ...,
  PartType_ID INT REFERENCES PartType(ID),
  ...
);

CREATE TABLE PartReviews (
  ...
  PartType_ID INT REFERENCES PartType(ID),
  ...
);

This will prevent your making a PartStats or a PartReview for a non-existent PartType.

If query performance becomes an issue, adding secondary indexes on PartType_ID will help.

Answer 5

I can recommend you a couple of not bad books on database design (several months ago I decided to improve my database design skills so I took a look at several different books and chose these two):

1) Pro SQL Server 2008 Relational Database Design and Implementation (c) Louis Davidson
2) Relational database design clearly explain (c) Jan Harrington

Good luck!