数据库架构设计优化了用于分类的评分行
我想要一个具有分数列的表格，并且每当basket1或basket2都添加到表中。 basket1 = [马铃薯，番茄，橙色] 和basket2 = [马铃薯，苹果，橙色]插入db中，我想从篮子里的物品中减去1，并增加篮子的分数。
因此，如果我有这样的桌子，当我插入5,6时，我想进行数学操作，
prod_id name score
---------------------------
1 potato 10 (-1, -1)
2 tomato 10 (-1)
3. orange 10 (-1, -1)
4. apple. 10 (-1)
5. basket1. 40 (+3)
6. basket2 40 (+3)
显然我可以查看篮子1阵列，并执行n个数量的DB查询来更新行，因为说篮子中是否有1000个项目，如果我们在1查询中更新了很多行，但DB会变得较慢/锁定，但是我试图弄清楚是否有任何优化的方法可以做到这一点，而对读取查询性能的影响很小。我认为的替代方案可能是为分数创建另一个表格，并跟踪那里的分数。还有其他任何方式，我可以智能布局DB模式，以使性能影响最低
I want a table like this which has scores column, and whenever basket1 or basket2 are added to table I want to update scores of the columns in basket.So say basket1 =[potato, tomato, orange] and basket2 = [potato, apple, orange] are inserted in db, I want to subtract 1 from items in baskets and increment the score for basket.
So if I have a table like this and when I insert 5,6 I want to do the math operation
prod_id name score
---------------------------
1 potato 10 (-1, -1)
2 tomato 10 (-1)
3. orange 10 (-1, -1)
4. apple. 10 (-1)
5. basket1. 40 (+3)
6. basket2 40 (+3)
Obviously I can look at basket 1 array and do N number of db queries to update rows, because say if there are 1000 items in basket, will DB become slower/locked if we are updating so many rows in 1 query, but I am trying to figure out if there is any optimized way where I can do this with minimal impact on read queries performance. Alternatives I thought were maybe create another table for scores and keep track of scores there. Are there any other way where I can intelligently layout the db schema such that performance impact is bare minimum
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我目前的数据模型看不到任何问题。保持聪明而简单。将数据写入PostgreSQL将永远不会锁定读数，因为它的多元素并发控制功能。
另外，每笔交易的1000或2000个更新的行也不是任何问题。但是，如果写作绩效不是问题，我建议您将尽可能少的并发工人使用，最多只能使用。这将有助于写作工作不会互相阻止。
从功能上的角度来看，我不会称ID为“ prod ID”，因为篮子不是产品。该表包含分数，因此更好地将其称为得分。但是，我不知道其余数据模型。
一般规则：
I can't see any issue with your current data model. Keep it smart and simple. Writing data to Postgresql will never lock reading because of its multiversion concurrency control feature.
Also 1000 or 2000 updated rows per transaction shouldn't be any issue, too. However, if write performance is no issue I would recommend that you use as less concurrent workers for writing as possible, at best only one. This will help that the writing jobs don’t block each other.
From a functional point of view I wouldn't call the id “prod id“ because baskets are no products. The table contains scores, so better call it scores. But of cause I don't know the rest of the data model.
General rules: