基本数据库设计：类型的实例列表 [英] Basic database design : list of instances of type

问题描述

这是一个关于数据库设计的初学者问题。假设我们有一个博客网站，许多用户，每个都有一些博客文章。我们想快速查找给定用户写的所有文章。我们可以使用给定的用户ID搜索帖子表中的所有博文。我们还可以设计用户表以列出用户的帖子。这意味着也许存储一串逗号分隔的帖子ID。这样做的正确方法是什么？

This is a beginners question about database design. Suppose we have a blogging website with many users, each of whom have a few blog posts. We want to quickly find all articles written by a given user. We could search the post table for all blog posts with the given userID. We could also design the user table to have a list of the user's posts. This would mean, perhaps, storing a string of comma separated post IDs. What is the proper way to do this?

推荐答案

您正在寻找数据库规范化，一种防止：

You're looking for database normalization, a technique which prevents:

• 冗余存储相同的数据不止一次）


• 异常（更改一个基准改变另一个）


• 循环（更改A更改C更改A ）


• 重新设计（添加或删除字段需要更改其他字段）


• 偏差（不同的方式提出相同的问题提供不同的答案） li>
  

• Redundancy (storing the same data more than once)
• Anomalies (changing one datum changes another)
• Cycles (Changing A changes B which changes C which changes A)
• Redesign (adding or removing a field requires changing other fields)
• Bias (different ways of asking the same question gives different answers)

标准化的典型形式称为 Boyce-Codd Normal Form ，一般来说很难做到，但是您可以通过实现较低的正常表格。wikipedia.org/wiki/First_normal_form

The typical form of normalization is called Boyce-Codd Normal Form and is, in general, quite difficult to do, but you can improve your design by implementing the lower Normal Forms.

您没有给我们足够的信息来为您推荐一个模式，但是如果需要，存储一串逗号分隔的帖子ID是错误的区分帖子ID。如果这是你想要的，你应该考虑如下设计：

You have not given us enough information to recommend a schema for you, but "storing a string of comma separated post IDs" is the wrong thing to do if you need to distinguish between post IDs. If that’s what you want you should consider a design like:

Users
userID    other user fields ..
   100    Charlie
   101    Edith

Articles
articleID  userID  pathOrWhatever...
     1000     100  http://example.com/stuff
     1001     100  http://example.com/moreStuff
     1002     101  http://example.com/somethingElse

此设计可以从数据库命令中获取用户或文章中的用户的文章。

This design can get articles from users, or users from articles, from database commands.

这篇关于基本数据库设计：类型的实例列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！