douwen1915 2011-06-13 10:07
浏览 23
已采纳

用于确定用户提交数据的值的数据库设计

I am creating a review site where certain details of a product is determined from the aggregated responses of users who have written review for a particular product. For example, when a user is reviewing the product Macbook Air, apart from rating it 1-5 stars and writing a 300 word description of his experience using the laptop, he can also do a short 'survey' containing checkboxes where he can choose if the product is recommended for:

  1. office suite
  2. games
  3. graphic design
  4. watching movies

For example, the user can select the checkbox for 'office suite' and 'watching movies'. Assume the case where all the responses of all the reviewers for this Macbook Air product results in 100 votes for 'office suite' and 50 votes, 20 votes and 10 votes for the other options. Because 'office suite' option have the most votes, on the product page of the Macbook Air, it will state:

Product recommended for: Office Suite

How will you go about designing the database for this? I am thinking of having a separate table with the columns 'rec_office_suite', 'rec_games', 'rec_graphic_design', 'rec_watching_movies' each containing the number of votes for that option. Every time a reviewer submits his review and fills up the mini survey, the database table will be updated with the fields he has chosen to be incremented +1.

The thing is that this can end up with a table with many fields. Will this be a problem?

  • 写回答

2条回答 默认 最新

  • douyao1856 2011-06-13 10:29
    关注

    I'd recommend a combination of your proposed solution in the question and Nobita's method.

    Nobita is correct in that using linked tables is the correct way of storing the data. It's great for later expansion (simply add a new category record). I'd start this way.

    When it comes to displaying the results on each product page though, it will be quite demanding on the database to count all votes for all categories for each product. So it makes sense to duplicate the data in a sort of cache for fast reading. Your idea of a table column for each category isn't easy to expand later though and could get very large. Instead I'd simply store the votes in a MEDIUMTEXT (or similar) column on the product table - perhaps in JSON format or a serialized PHP array - which is very simple to read. The cache only needs updating whenever someone adds a category vote, or when a new category is added.

    By using both you have easy access to product/category/vote data for more complex SQL queries later on, and fast queries for product pages.

    Hope that makes sense, half asleep :)

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题