数据库建模：Facebook喜欢的消息 [英] Database Modeling: Facebook like messages

问题描述

我试图模仿类似于FB的东西。基本上，用户可以在用户简档的各个部分（例如墙，照片等）中发布评论。我认为以下模型将有效：

  ================ ========= 
 wall_message 
 ========================= 
  - id（PK）
  -  parent_id（FK）
  -  wall_owner_profile_id（FK，标识消息所在的墙）
  -  poster_profile_id（FK）
  -  message 
  - 时间戳
 
 ========================= 
 media_message 
 ===== ====================== 
  -  id（PK）
  -  parent_id（FK）
  -  media_id（FK，identify哪些照片，视频等）
  -  poster_profile_id（FK）
  -  message 
  -  timestamp 
  

$ b b

parent_id 允许将消息分组为相关讨论。第一个消息的 parent_id 将为0，后续消息将使用PK作为 parent_id 值（创建父子

poster_profile_id 表示发布讯息的人。

上面的两个表非常相似。将它们组合是一个好主意，例如：

  ============= ============== 
 message 
 =========================== 
  -  id（PK）
  -  parent_id（FK）
  -  type（ENUM：wall，media等）
  -  types_id（FK， ）
  -  poster_profile_id（FK）
  -  message 
  -  timestamp 
  

在这种情况下，如果 type 是wall，则 types_id 等于第一个表的 wall_owner_profile_id。如果 type 是media，则 types_id 等于第二个表的 media_id 。

我有点担心第二种方法需要一个列来解释另一列的含义。这个缺点是，我想，对types_id没有参照完整性（与wall_owner_profile_id和media_id不同）。

是解决这个问题的最佳方法吗？

编辑1：

到目前为止的解决方案：

  ======================= ====== 
 message 
 ========================= 
  -  message_id（PK ）
  -  parent_message_id（FK）
  -  profile_id（FK，指发布消息的人）
  - 消息
  - 主题（仅适用于电子邮件）
  -  timestamp 
 
 ========================= 
 wall_message 
 ====== ===================== 
  -  message_id（FK）
  -  profile_id（FK，表示收到消息的人/墙的所有者） 
 
 =========================== 
 media_message 
 ====== ===================== 
  -  message_id（FK）
  -  media_id（FK）
 
 === ====================== 
 email_message 
 ================= ========== 
  -  message_id（FK）
  -  profile_id（FK，指收到邮件的人）
  

解决方案

首先，对小点做几个回应，以保持你在关系数据库和数据库设计的直接和狭窄的路径。 / p>

1. 整个想法是在数据库中放置尽可能多的规则， / em>。几乎一切都可以通过DDL：FK约束; CHECK 约束;和 RULES （所有ISO / IEC / ANSI SQL要求）。然后所有的用户（你的应用程序是一个用户）可以看到所有的规则，并更好地了解数据库。这保护了数据库，无论使用什么客户端来执行代码。




2. 这些约束的Db供应商（即商业，非免费软件）实现比代码更可靠。表是父行必须首先存在。这就是FK约束的作用，它确保父行存在。在多对多表中，两个父行必须存在，才能插入子代（具有两个FK，每个父代一个）。




3. code> types_id 是一个可怕的想法，因为你已经破坏了设计规则，并删除了RI的可能性。更好地有单独的列与RI（每个父的FK约束）。 （但有更好的方法。）




4. 所有的 Id 可以重命名为 TableId 。每个应该有相同名称的Private DataType。列名在所存在的任何位置不变，作为FK使用。唯一的例外是你有两个FKs到同一个父表：那里应该是 RoleTableId 。

这是解决这个问题的最佳方法是什么？

你会遇到暴露的问题，你需要解决。因此再次标准化。

1. 您的单个​​消息表已经有一半的时间了。您已经直观地将两个表归一化为一个。

   
   
   
   
   
   • Sebastian提供了两个多对多的表，所以我赢了' t重复。
     
     。
   
   



2. 在决定这是最终之前到多个表是最终的），我建议你规范化墙和媒体。对我来说，它看起来像有许多常见的列。如果你规范化，你会得到一个表。由于它是由 Person 为了邀请消息而暴露或提供的Thing，类型可以 {Photo |相册|邮箱| Wall} ，我将其称为 PersonFurniture 或 PersonObject 。

   
   
   
   
   
   • 如果结果是一个表，那么你不需要两个多对多表，只需要一个。
   
   

对注释的回复

1. 绘制模型比输入长讨论更容易和更快。我想过你的大部分问题。请检查这一点，并对任何你不明白的问题提出具体问题。

指向社交网络数据模型的链接 （第3页）

链接到IDEF1X记法 ，以便那些不熟悉的人关系建模标准。

• 选择您自己的表和列名称


• Message.Subject 可以设置为 CHAR（0）或忽略，如果它不是电子邮件。


• wall_message 和 email_message 是相同的，是不是无论是 wall_message 还是 email_message 或 code> media_message 是一个问题，它是发送，对不对？您可以通过CHECK约束轻松禁止任何邮件类型的任何功能（例如分组）。


• 您尚未回答上述（2）


• 我认为消息分组与媒体分组不同：考虑相册上是否有消息列表。


• 没有什么是问题，整个建模的想法是，纸张是低廉;关系dbs的整体思想是，尽可能多地使用约束，检查，规则。

（你的种族问题中你想要种族（3个关卡）还是2个关卡？）

I'm trying to mimic something similar to FB. Basically, users can post comments in various parts of a user's profile (e.g. "wall", a "photo", etc.). I think the following model will work:

===========================
wall_message
===========================
- id (PK)
- parent_id (FK)
- wall_owner_profile_id (FK, identify whose wall the message is for)
- poster_profile_id (FK)
- message
- timestamp

===========================
media_message
===========================
- id (PK)
- parent_id (FK)
- media_id (FK, identify which photo, video, etc.)
- poster_profile_id (FK)
- message
- timestamp

parent_id allows messages to be "grouped" into a related discussion. The first message's parent_id will be 0 and subsequent messages will have the PK as the parent_id value (creating a parent-child relationship).

poster_profile_id identifies who posted the message.

The above two tables are very similar. Would it be a good idea to combine them, such as:

===========================
message
===========================
- id (PK)
- parent_id (FK)
- type (ENUM: "wall", "media", etc.)
- types_id (FK, see explanation below)
- poster_profile_id (FK)
- message
- timestamp

In this case, if, say, type is "wall", then types_id is equal to the first table's "wall_owner_profile_id". If, say, type is "media", then types_id is equal to the second table's media_id.

I'm a bit concerned that the second approach requires a column to explain the meaning of another column. A disadvantage to this, I suppose, is that there would be no referential integrity for types_id (unlike for "wall_owner_profile_id" and "media_id").

What would be the best way to tackle this problem?

EDIT 1:

Seems like this is the solution so far:

===========================
message
===========================
- message_id (PK)
- parent_message_id (FK)
- profile_id (FK, referring to who posted the message)
- message
- subject (applicable only for emails)
- timestamp

===========================
wall_message
===========================
- message_id (FK)
- profile_id (FK, referring to who received the message/owner of wall)

===========================
media_message
===========================
- message_id (FK)
- media_id (FK)

===========================
email_message
===========================
- message_id (FK)
- profile_id (FK, referring to who received the message)

解决方案

First, a few responses to small points, to keep you on the straight and narrow path of Relational databases and db design.

1. The whole idea is to place as many of the Rules right in the database, in one place, and not in code. Almost everything can be done via DDL: FK constraints; CHECK constraints; and RULES (all ISO/IEC/ANSI SQL requirements). Then all the users (your app is an user) can see all the rules and understand the database better. That protects the db, no matter what client is used to execute the code. Db vendors (that means commercial, not freeware) implementation of these constraints are more reliable than code.

2. The requirement (not convention) for inserting rows to a child table is that the parent row must exist first. That is what the FK constraint does, it ensures the parent row exists. In a many-to-many table, both parent rows must exist before the child (with two FKs, one to each parent) can be inserted.

3. types_id is a horrible idea because you have broken design rules, and removed the possiblity of RI. Better to have separate columns with RI (FK constraints to each parent). (But there is an even better way.)

4. All yourId columns, the PKs, should be renamed TableId. Each should have Private DataType of the same name. The column name is used unchanged wherever it exists, as an FK. The only exception is where you have two FKs to the same parent table: there it should be RoleTableId.

What would be the best way to tackle this problem?

Normalise. And you will have issues that are exposed, which you need to resolve. Therefore Normalise again. And keep doing that until you have no issues to resolve.

1. Your single Message table is already half way there. You have intuitively Normalised the two tables into one. But there are issues to resolve, so let's handle them.

   • Sebastian has provided the two many-to-many tables, so I won't repeat.
     .

2. Before you decide that that is final (and therefore the two many-to-many tables are final), I suggest you Normalise Wall and Media. To me, it looks like there are many common columns. If you Normalise that, you will get one table. Since it is a Thing that is exposed or furnished by a Person for the purpose of inviting Messages, and the type can be{ Photo | Album | Mailbox | Wall }, I would call it PersonFurniture or PersonObject.

   • If that ends up as one table, then you won't need two many-to-many tables, just one.

Responses to Comments

1. It is easier and faster to draw the model, than to type long discussions. I have thought about most of your questions. Please check this and ask specific questions about anything you do not understand.

Link to Social Network Data Model (Page 3)

Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.

• CHoose your own table and column names
• Message.Subject can be set to CHAR(0) or ignored, if it is not Email.
• that wall_message and email_message are identical is not a problem, I've Normalised them into one table
• whether it is a wall_message or email_message or media_message is a matter of where it is "sent", right ? You can easily disallow any function (eg. grouping) for any message type via a CHECK constraint.
• you haven't answered (2) above
• I think message grouping is different from media grouping: think about when a photo album has a list of messages on it.
• nothing is a problem, the whole idea of modelling is, paper is cheap; the whole idea of Relational dbs is, to do as much as possible using constraints, checks, rules. If anything is wrong we can change it.

(Do you want Race (3 levels) or 2 levels in your Ethnicity question ?)

这篇关于数据库建模：Facebook喜欢的消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！