I have to make 100 approximately tables. I've tables known as PartStatsXXX and also the tables to become made all will be known as PartReviewXXX (they pair track of one another inside a 1:n relationship).
Could it be efficient to produce one large table to keep all product (product and part being exactly the same term from the business perspective) reviews? Someone pointed out creating a relationship from PartStatsXXX to PartsReview (one large table) with the need for XXX included in the primary key from PartStatsXXX.
XXX may be the title from the part type (eg battery, wiring loom, etc). So this is varchar. Must I create a composite key? The part type wouldn't change names (though some part names might have multiple names based on culture), but it is not just a candidate ID. It had been then pointed out I possibly could get several sights for which I want with respect to the worth of XXX.
I really hope this will make sense. What will be the ultimate way?
Multi-table PartStatsXXX is an awful idea: difficult to code correctly or having a framework, harder to keep, nightmare to question...
Use two tables: PartStats and PartsReview, with approriate secrets and indexes for performance.
It's more effective to produce tables according to what you would like to keep in every one. You don't need 100 tables for 100 items. you'll need 1 table for those items.
So to your requirements I'd create 2 tables:
products ======== id INT name VARCHAR product_reviews =============== id INT product_id INT (foreign key to products.id) rating INT (example column)
Unless of course you're storing various kinds of data for every product's reviews (i.e., each table includes a different group of posts), utilizing a different table per product is going to be creating a pointless nightmare.
Typically, you won't ever wish to have several table with similar group of posts. As already recommended, one table having a "product_id" column is what you want.
If you wish to save some discomfort inside a quick-and-dirty way, use two tables.
CREATE TABLE PartStats ( ..., PartType VARCHAR(255), ... ); CreateTable PartReview ( ... PartType VARCHAR(255), ... );
after which join them up via
SELECT ... FROM PartStats ps JOIN PartReview pr ON ps.PartType = pr.PartType;
This will get you out of trouble from getting 100s of tables, but sets you up for any different problem: Redundant data (PartType) that may get free from sync. A typo inside a PartType can yield an orphaned review.
The answer here, presuming that you could have several PartStats entry for any given PartType, would be to give a third table towards the sole older of PartType names.
CREATE TABLE PartType ( ID INT ..., PartType VARCHAR(255), PRIMARY KEY (ID) );
and request PartStats and PartReview to make use of the ID of the PartType. For instance,
CREATE TABLE PartStats ( ..., PartType_ID INT REFERENCES PartType(ID), ... ); CREATE TABLE PartReviews ( ... PartType_ID INT REFERENCES PartType(ID), ... );
This can stop your creating a PartStats or perhaps a PartReview for any non-existent PartType.
If query performance becomes an problem, adding secondary indexes on PartType_ID can help.
I'm able to recommend you a few pretty good books on database design (several several weeks ago I made the decision to enhance my database design abilities and so i required a glance at a number of different books and chose both of these):
1) Professional SQL Server 2008 Relational Database Design and Implementation (c) Louis Davidson
2) Relational database design clearly explain (c) Jan Harrington
Best of luck!