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

本文是小编为大家收集整理的关于数据库建模。脸书喜欢的消息的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我试图模仿类似于fb的东西.基本上,用户可以在用户个人资料的各个部分(例如" Wall","照片"等)中发布注释.我认为以下模型将起作用:

===========================
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允许将消息"分组"到相关的讨论中.第一个消息的parent_id将为0,后续消息将使PK作为parent_id value(创建父子关系).

poster_profile_id标识谁发布了消息.

上面的两个表非常相似.结合它们是一个好主意,例如:

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

在这种情况下,如果说type是"墙",则types_id等于第一表的" wall_owner_profile_id".如果说type是"媒体",则types_id等于第二个表的media_id.

我有点担心第二种方法需要一列来解释另一列的含义.我想这是一个缺点,是types_id没有参考完整性(与" wall_owner_profile_id"和" media_id"不同).

解决此问题的最佳方法是什么?

编辑1:

似乎这是到目前为止的解决方案:

===========================
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)

推荐答案

首先,对小点的一些响应,以使您处于关系数据库和数据库设计的直线和狭窄路径.

  1. 整个想法是将数据库中的许多规则放在一个地方,而不是代码中的 .几乎所有内容都可以通过DDL:FK约束来完成; CHECK约束;和RULES(所有ISO/IEC/ANSI SQL要求).然后,所有用户(您的应用程序是用户)都可以查看所有规则并更好地了解数据库.无论使用哪种客户端执行代码,都可以保护数据库.数据库供应商(这意味着商业,而不是免费软件)这些约束比代码更可靠.

  2. 将行插入子表中的要求(不是惯例)是首先存在父行.这就是FK约束所做的,它确保了父行的存在.在多对多的表中,必须插入孩子(用两个FK,一个对每个父母)之前的两个父行都存在.

  3. types_id是一个可怕的想法,因为您打破了设计规则,并消除了RI的可能性.最好将带有RI的单独列(对每个父级的FK约束). (但是有更好的方法.)

  4. 您的所有Id列,PKS,应重命名为TableId.每个人都应具有同名的私有数据类型.该列名在存在的任何地方都不会变化,作为FK.唯一的例外是您在同一父表中有两个FK:应该有RoleTableId.

解决此问题的最佳方法是什么?

归一化.而且您将存在暴露的问题,您需要解决.因此再次归一化.并继续这样做,直到您没有解决问题为止.

  1. 您的单个​​消息表已经在其中一半.您将两个表格将其标准化为一张.但是有一些问题要解决,所以让我们处理它们.

    • 塞巴斯蒂安(Sebastian .
  2. 在您决定最终(因此,两个多一对多的表是最终表)之前,我建议您归一化Wall和Media.对我来说,看起来有许多常见的列.如果将其归一化,您将获得一张桌子.由于它是由A Person暴露或提供的东西,以邀请Messages,并且类型可以为{ Photo | Album | Mailbox | Wall },所以我称其为PersonFurniture或PersonObject.

    .
    • 如果那是一张桌子,那么您将不需要两个多一对一的表,只有一个.

对评论的响应

  1. 绘制模型比键入长期讨论更容易,更快.我已经考虑了您的大多数问题.请检查一下,并询问有关您不了解的任何内容的具体问题.

链接到社交网络数据模型> (第3页)

/a>对于那些不熟悉关系建模标准的人.

  • 选择自己的表格和列名
  • Message.Subject如果不是电子邮件,可以设置为CHAR(0)或忽略.
  • wall_message和email_message是相同的不是一个问题,我将它们标准化为一个表
  • 它是wall_message还是email_message或media_message是"发送"的问题,对吗?您可以轻松地禁止通过检查约束的任何消息类型的任何功能(例如分组).
  • 您尚未回答(2)
  • 我认为消息分组不同于媒体分组:考虑何时有相册有一系列消息.
  • 没有什么问题,建模的整个想法是,纸是便宜的.关系DBS的整个想法是,使用约束,检查,规则尽可能多地做.如果有什么问题,我们可以更改它.

(您想要种族(3级)或种族问题中的2个级别?)

其他推荐答案

您可以包含表邮件,然后可以发表n:m关系表,即

message_to_wall:
- messageID
- wallID

message_to_media:
- messageID
- mediaID

这样,您可以保留参考完整性,只有一个消息表.

当然,从技术上讲,这将允许它向墙壁和媒体项目(照片等)发布消息.因此,您无法轻易限制这一点.

否则 - 如果您真的不需要关系数据库,则可以考虑使用NOSQL数据库,例如CouchDB或MongoDB.您可以将所有这些评论存储在墙壁或媒体文档上.这样,您就没有所有必需的加入查询,并且评论都链接到媒体或墙.

本文地址:https://www.itbaoku.cn/post/597756.html

问题描述

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 ?)

其他推荐答案

You could have your table message, and then n:m relationship tables, i.e.

message_to_wall:
- messageID
- wallID

message_to_media:
- messageID
- mediaID

This way you keep the referential integrity and only have one message table.

This of course would technically allow it to have a message posted to a wall AND to a media item (photo, etc.). So you can't easily restrict this.

Otherwise - if you do not really require a relational database, you could think about using a NoSQL database like CouchDB or MongoDB. You can store all those comments right on the wall or media document. That way you don't have all those required JOIN queries and the comments are all linked to the media or wall.