数据库关系设计 - 在不同的表中关联两个表两次
我有以下表格：
Post
Id int
User
Id int
然后我有表格
Favorite
PostId int
UserId int
和表格，
Vote
PostId int
UserId int
IsUpVote bit
IsDownVote bit
LastActivity datetime2
问题是，如果我将“收藏夹”和“投票”合并到一个表格中，那么我将
UserPost
PostId int
UserId int
IsFavorited bit
IsUpVoted bit
IsDownVoted bit
LastActivity datetime2
无法计算诸如 IsDownVote 之类的内容（从现在起，我不能再使用“不存在：没有投票；没有投票赞成：投票反对”模式）并且 LastActivity 将仅反映最后一次投票已更改（向上、向下或删除）。所以我可能必须更改该字段的名称或其功能。甚至两者都......
所以问题基本上是，在这种情况下，有两个与表 A 和 B (Post,User) 相关的表是多么错误，它们由相同的主键 (在这种情况下，PostId,UserId)，但是它们有不同的用途？
I have the following tables:
Post
Id int
User
Id int
Then I have the table
Favorite
PostId int
UserId int
and the table
Vote
PostId int
UserId int
IsUpVote bit
IsDownVote bit
LastActivity datetime2
the problem is that if I merged both Favorite and Vote into a single table, then I'd have something like
UserPost
PostId int
UserId int
IsFavorited bit
IsUpVoted bit
IsDownVoted bit
LastActivity datetime2
IsDownVote couldn't be computed anymore (since now, I can't use a "doesn't exist: didn't vote; didn't vote up: voted down" pattern anymore) and LastActivity will only reflect the last time the vote has changed (either up, down, or removed). So I'd maybe have to change that field's name or it's functionality. or even both..
So the question is basically, how wrong is having two tables relating Tables A and B (Post,User) in this case, which are indexed by the same primary key (PostId,UserId) in this case, but which are intended for different uses?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
收藏夹和投票似乎是两个不同的东西，所以恕我直言，您最好将它们保留为单独的表。正如您所提到的，如果合并它们，您将失去功能，而且我没有看到合并它们有任何明显的好处。坚持现有的做法，除非您能为合并提供一个令人惊叹的理由。
Favourites and Votes seem to be two different things, so IMHO you will be better off keeping them as separate tables. As you mentioned, you would lose functionality if you merged them, and I don't see any clear benefit to merge them. Stick with what you've got unless you can provide an awesome justification for the merge.
完全没有错。
我并不是说提供的 DDL 正确显示了规范化表，但它们在某种程度上是规范化的。正如您自己所确定的，这两个表有不同的目的，它们有不同的含义，因此从技术上（理论上，学术上和实践上[代码]）来说，它们是正确的。
只有没有真正的标准化概念，也没有负面表现原因概念的人，才会建议“仅仅因为他们有相同的父母（因此有相同的键/索引对）”，他们应该被合并。
投票和收藏是两个不同的事物、实体、所采取行动的记录。两张表是正确的。
区别：IsDownVoted 无法再进行比较的真正原因是它不适用于 Favourite。您已使用指标（位）来识别该指标（尽管名称不好）；这实际上是空列的替代品。空值对性能不利，有指标来识别数据的缺失是一件好事，因此可以避免空值，但这与通过合并它们来破坏规范化设计是分开的。
合并后的表在所有访问中的执行速度都会变慢。当您从中选择投票时，您必须排除收藏夹，反之亦然，但它将为两者执行 I/O，因为它们位于在一起（PostId、UserId）。因此服务器永远读取两倍的行数，使用两倍的缓存；然后，您将通过为（PostId、UserId、IsFavourited）添加索引来“增加速度”，从而使插入和删除速度更慢（同时“加速”选择）。混乱会变得更加复杂，这是肯定的；最好一开始就不要有任何混乱。
当数据库增长时，您可以独立地将列添加到 Vote 和 Favourite 之一，而不会影响另一个。在合并表中，它会带来复杂性。
您接受答案的速度太快了。
Nothing wrong at all.
I am not saying that the DDL provided shows correctly Normalised tables, but they are somewhat Normalised. As you have identified yourself, the two tables have different purposes, they have different meaning, so technically (theoretically, academically, and in practice [code] ), they are correct.
Only someone with no real concept of Normalisation, and no concept of the causes of negative performance, will suggest that "just because they have the same parents (and therefore the same pair of keys/indices)", they should be merged.
Vote and Favourite are two different Things, Entities, records of Action taken. Two tables is correct.
Distinction: The real reason IsDownVoted cannot be compared anymore is that it does not apply to Favourite. You have used an Indicator (bit) to identify that (although badly named); which is really a substitute for a Null column. Nulls are not good for performance, and it is a Good Thing that you have Indicators to identify the absence of data, and therefore avoided Nulls, but that is separate to breaking a Normalised design by mereging them.
The merged table will perform slower on all accesses. When you SELECT Votes from it, you have to exclude Favourites, and vice versa, but it will be doing I/O for both, because they are located together (PostId, UserId). SO the server is forever reading twice as many rows, using twice as much cache; etc. Then you will "add speed" by adding an index for (PostId, UserId, IsFavourited), making it even slower for Inserts and Deletes (while "speeding up" Selects). Messes get compounded, guaranteed; best to not have any mess in the first place.
When the database grows, you can independently add columns to either one of Vote and Favourite, without affecting the other. In a merged table, it will introduce complications.
You accept Answers too quickly.
虽然我不会说如果您使用 int 而不是 bit 并使用 0 1 和 -1 等值进行计算/比较，您应该在表中做什么，这样您就可以以相对简单的方式计算您想要的值。
谈论关系数据库时，您几乎应该始终针对表的第三范式 - 尝试查看 http:// en.wikipedia.org/wiki/Database_normalization
干杯！
While I won't say what you should do table wise if you use int instead of bit and use values like 0 1 and -1 to do calculations / comparisons, this way you could compute the values you want in a relatively simple way.
Talking relational databases you should almost always aim for 3'rd normal form regarding your tables - Try looking at http://en.wikipedia.org/wiki/Database_normalization
Cheers!