社交网站应用程序数据库设计：如何改进这个模式？

Question

背景

我正在开发一个面向诗人和作家的社交网络应用程序，允许他们分享自己的诗歌、收集反馈并与其他诗人进行交流。我在数据库设计方面几乎没有正式的培训，但我一直在阅读书籍、SO和在线数据库设计资源，以确保性能和可扩展性而不会过度工程化。

该数据库使用MySQL，应用程序使用PHP编写。我还不确定我们是否会在应用程序中使用ORM库或从头编写SQL查询。除了Web应用程序外，Solr搜索服务器和可能的消息客户端将与数据库交互。

当前需求

我下面提供的模式表示网站第一个版本的主要组件。最初，用户可以注册网站并执行以下任何操作：

• 创建和修改个人资料详情和帐户设置
• 发布、标记和分类自己的作品
• 阅读、评论和“收藏”其他用户的帖子
• “关注”其他用户以获取其活动的通知
• 搜索和浏览内容并获取推荐的帖子/用户 (尽管我们将使用Solr搜索服务器索引DB数据并运行这些类型的查询)

模式

以下是我在MySQL Workbench上为初始网站想出来的内容。我对某些关系型数据库的东西还有点迷糊，所以请多多包涵。

问题

1. 总的来说，我是否做错了什么或可以改进？
2. 有没有理由不将ExternalAccounts表合并到UserProfiles表中？
3. 有没有理由不将PostStats表合并到Posts表中？
4. 我应该扩展设计以包括我们在第二个版本中要做的功能，以确保初始模式可以支持它吗？
5. 有什么可以优化数据库设计以进行Solr索引/性能/其他方面的操作吗？

我是否应该使用更自然的主键，例如用户名代替用户ID，或者邮政编码/地区代码代替位置表中的代理主键LocationID？

感谢您的帮助!

- Arjun

---

请不要将SQL查询放入应用程序中的嵌入字符串中。请考虑使用存储过程或类似方法来代替。拜托了。 - JUST MY correct OPINION

抱歉，无论如何，我都会使用存储过程/在查询和应用程序的其他部分之间构建一些基本的抽象。 - Arjun

也许可以添加“UserPreferences”来记录用户偏好的类别等信息，可以新建一张表或者将其纳入到用户资料中。 - igelkott

Answer 1

总体而言，我没有看到您当前设置或架构中的任何大问题。

我想知道的是您将用户分为三个User*表。我理解您的意图是要将不同的与用户相关的内容分开，但我不确定我是否会采取完全相同的做法。如果您计划仅在网站上显示来自User表的数据，则这很好，因为其他信息不需要在同一页上多次使用，但如果用户需要使用他们的真实姓名并显示他们的真实姓名（例如John Doe而不是doe55），那么当数据变得更大时，这将减慢速度，因为您可能需要连接。将Preferences分开似乎是个人选择。我对此没有支持或反对的论据。

您的多对多表不需要额外的PK（例如PostFavoriteID）。由于PostFavoriteID在其他地方从未使用过，因此仅组合Primary Key的PostID和UserID就足够了。所有连接表都适用此规则

像之前的答案一样，我看不出优劣势。我可能会将两者放在同一张表中，因为NULL（或者最好是-1）值不会困扰我。

我会把它们放入同一个表中，并使用触发器处理ViewCount表的增量

您正在使用标准化的模式，因此可以随时进行任何添加。

我无法告诉您如何优化Solr索引/性能/任何内容的DB设计，因为我还没有这样做过，但我知道Solr非常强大和灵活，所以我认为您应该做得很好。

有许多SO线程讨论此问题。个人而言，我更喜欢使用替代键（或另一个唯一数字键，如果可用），因为它使查询更加简单快捷，因为int更容易查找。如果允许更改用户名/电子邮件/您的PK等，则需要进行大量更新。使用替代键，您不需要担心。

我会建议你添加一些统计数据，例如created_at、last_accessed等（最好通过触发器或存储过程完成），这样就可以提供一些有价值的统计数据。

进一步提高性能的策略可能包括使用memcache、计数缓存、分区表等。当你真正面临用户超载时，可以讨论这些问题，因为可能有些技术或技巧非常适合解决你的问题。

- DrColossos

Answer 2

我不清楚你的用户*表格在做什么 - 它们设置得好像是1:1，但图表反映了1对多（用乌鸦脚符号表示）。

ExternalAccounts和UserSettings可以进一步规范化（这样它们就会变成1对多！），这将为您提供更可维护的设计 - 您不需要为其他外部帐户或通知类型添加更多列到您的模式中（尽管这可能在性能方面不太可扩展）。

例如：

ExternalAccounts
    UserId int,
    AccountType varchar(45),  
    AccountIdentifier varchar(45)

将允许您在相同的结构中存储LinkedIn、Google等帐户。同样，可以使用类似以下结构轻松添加更多通知类型：

UserSettings
    UserId int,  
    NotificationType varchar(45),  
    NotificationFlag ENUM('on','off')

hth

- richaux