何时将单独的SQL数据库表用于两种稍有不同的信息类型？ [英] When to use separate SQL database tables for two slightly different types of information?

问题描述

我需要一个使我感到困惑的SQL决策方面的帮助。

I need help with an SQL decision that has confused me for a while.

我正在尝试创建一个短故事网站，用户可以在其中编写自己的故事并可以互相浏览等。我还收集了过去伟大作家写的经典短篇小说集。我对于是否应该将两种类型的故事都存储在同一数据库表中感到困惑。

I'm trying to make a short story website where users can write their own stories and can browse each other's, etc. I've also got a collection of classic short stories written by great writers from the past. I'm confused as to whether I should store both types of story in the same database table.

我想保留两种类型的故事（经典作者/用户）在某种程度上与众不同，因为您应该能够搜索网站并从结果中过滤出用户故事。但是我不能只在表中有一个数据库行来表示这一点，即布尔CLASSIC，因为对于经典的短存储，其他几行也将有所不同-没有用户，日期将为YYYY（即1869），而不是用户提交时的完整日期时间。

I want to keep the two types of stories (classic authors/users) distinct to some degree, since you should be able to search the website and filter out user stories from the results. But I can't just have a single database row in the table to represent this, ie a boolean CLASSIC, since with classic short stores, several other of the rows would be different too - there is no user, the date would be YYYY (ie, 1869) instead of a full datetime when the user submitted it.

但是我也不能完全将它们放在单独的表中。当大多数属性都相同时，我是否真的应该有两个不同的简短故事数据库表？目前，我将NULL填入经典短篇小说的用户行中，并且经过过滤的搜索具有仅搜索经典的选项，该选项从用户为NULL的数据库中进行选择。但是，当您在可能包含数百万个用户故事的庞大数据库中搜索仅几千个经典故事时，这似乎会影响性能。

Yet I can't quite justify putting them in separate tables either. When most of the attributes are the same, should I really have two different database tables for short stories? At the moment I am filling in NULL into the user row for classic short stories, and my filtered search has an option to search only through classics, which selects from the database where user is NULL. This seems to hit performance though, when you're searching through a huge database of potentially millions of user stories just to find a few thousand classic stories.

请注意，其他表也是如此，例如故事的标签，链接到短故事表。

Note that there are other tables too, like tags for the stories, linked to the short stories table.

所以我基本上是问SQL专家-是否有足够的理由将两者分开信息类型分为不同的表？我目前在开发中使用SQLite，但稍后会切换到MySQL或PostgreSQL。

So I'm basically asking you SQL experts - is there enough justification for separating the two types of information into different tables? I'm currently using SQLite in development but will switch to MySQL or PostgreSQL later.

推荐答案

我可能会选择父子表结构，其中您在表之间具有匹配的主键，例如：

I'd probably go with a "parent-child" table structure, where you have matching primary keys across tables, something like:

Stories: StoryId (PK), StoryType (U or C), StoryText, etc. (all of the shared stuff)
UserStories: StoryId (PK and FK), UserId, etc.
ClassicStories: StoryId (PK and FK), AuthorName, etc.

然后，如果需要，您可以围绕它们建立两个视图：

Then if you want, you can build two views around them:

V_UserStories: StoryId, StoryText, UserId, etc.
V_ClassicStories: StoryId, StoryText, AuthorName, etc.

使用此设置，您不会浪费任何列，将共享的内容放在一起，同时仍然可以轻松地保留两种类型的故事在逻辑上，如果需要它们是分开的。

With this setup, you're not wasting any columns, you're keeping shared stuff together, while still keeping the two types of stories easily logically separate if you need them.

这篇关于何时将单独的SQL数据库表用于两种稍有不同的信息类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！