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

问题描述

这是一个关于数据库设计的初学者问题.假设我们有一个有很多用户的博客网站，每个用户都有一些博客文章.我们希望快速找到给定用户撰写的所有文章.我们可以在 post 表中搜索所有具有给定用户 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改变B改变C改变A)
• 重新设计(添加或删除一个字段需要更改其他字段)
• 偏见(不同的提问方式给出不同的答案)

标准化的典型形式称为Boyce-Codd 范式并且通常很难做到，但您可以通过实施较低的Normal Forms.

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屋！