在MySQL中必要时存储冗余信息或连接表是否更好？
I have an online shop where users can have little shops with their own products. Each of this products can have questions associated to it and the owner of the shop has the ability to answer those questions. This information is stored in 3 tables a "Questions"(QuestionID,ProductID,...) table, a "Products"(ProductID,ShopID,...) table and a "Shop"(ShopID,OwnerID,...) table.
Is it better to have a ShopID in the 'Questions' table (to allow a shop owner to view all his questions) or to join those three tables to get Questions matching a certain Shop?
我有一个网上商店，用户可以在这里购买带有自己产品的小商店。 这些产品中的每一个都可以有与之相关的问题，并且商店的所有者有能力回答这些问题。 此信息存储在3个表中，包括“问题”（QuestionID，ProductID，...）表，“产品”（ProductID，ShopID，...）表和“商店”（ShopID，OwnerID，...） 表。 p>
在“问题”表中设置ShopID（允许店主查看他的所有问题）或加入这三个表以获得与某个商店匹配的问题更好吗？ p> div>
It is almost always better to join and avoid redundant information. You should only denormalize when you must do so in order to meet a performance goal - and you can't know if you need to do this until you try with normalized tables first.
Note that denormalization helps in read performance at the expense of slowing down writes and making it easier for a coding mistake to cause data to be out of sync (since you're storing the same thing in more than one place you now have to be sure to update it all).
Generally it is better to avoid redundant information. This seems like it should be quite a cheap join to do given appropriate indexes and I wouldn't denormalise in that manner unless I saw in the query plans that the JOIN was causing problems (perhaps because of the number of records in the tables)
You would also need to consider the ratio of reads to writes. Denormalisation will help the reads but add overhead to writes.
You should have a many to many relationship between the questions and the products:
questions_ref(question_id, question_code, question)
product_questions(pquestion_id, question_id_fk, product_id_fk)
products(product_id, product_name, etc)
If it is possible for the product to be in more than one shop (which im certain it is) you should also have a many to many relationship between the shops and products.
shop_products(sproduct_id, product_id_fk, shop_id_fk, sproduct_price, other_shop_specific_param)
shops(shop_id, owner_id_fk, shop_name, etc)
i think your design is okay. I wont add ShopID to table Questions. You should use a join, where necessary.
BTW: You shoud use a m:n relation between products and shops and remove ShopID for Products. So you can have the same product in differnent shops and also the same questions for a product.
Regards, Lars
From a design point of view, storing redundant data is not necessary. In your case it might be. Try to make some tests and if the query time is improved due this redundancy then you should proceed with the denormalization.