数据库表之间的关系不共享ERD中的任何外键

Consider an example of following database tables:

考虑以下数据库表的示例:

user ( id [PK], user_name, ... )

user(id [PK],user_name,...)

comment ( id [PK], u_id [FK], ... )

评论(id [PK],u_id [FK],...)

reportedCommentInfo ( id [PK], c_id [FK], reporting_u_id [FK], date, ... )

reportedCommentInfo(id [PK],c_id [FK],reporting_u_id [FK],date,...)

admin ( id [PK], user_name, ... )

admin(id [PK],user_name,...)

According to this database schema any user can post a comment and can report other user comments based on containing abusive or spamming material. Admin can view a list of all reported comments by users and can remove comments not complying to set standards.

根据此数据库架构,任何用户都可以发布评论,并可以根据包含滥用或垃圾邮件材料报告其他用户评论。管理员可以查看用户所有报告的评论列表,并可以删除不符合设置标准的评论。

In this scenario admin doesn't actually have access to any of the reported comments i.e. their id(s) are not present inside the admin table, yet admin can access and manage reported comments. So while creating ERD will their be a relation between reportedCommentInfo and admin table?

在这种情况下,管理员实际上无法访问任何报告的评论,即他们的ID在管理表中不存在,但管理员可以访问和管理报告的评论。那么创建ERD时它们会是reportingCommentInfo和admin表之间的关系吗?

i.e. basically my question is, while creating ERD/data model can we create a relationship between two tables which do not have any primary key / foreign key relationship?

即基本上我的问题是,在创建ERD /数据模型时,我们可以在两个没有任何主键/外键关系的表之间创建关系吗?

or two tables can only be related if they have primary key / foreign key relationship among them.

或两个表只有在它们之间具有主键/外键关系时才能相关。

p.s: any suggestions for improving anything in either database schema or ERD structure is more than welcome.

p.s:任何改进数据库模式或ERD结构中任何内容的建议都非常受欢迎。

2 个解决方案

#1

If any admin can view any comment, there's no need to model a relationship between them. Don't confuse data modeling with system modeling. The data model only needs to model facts that you want to record. If you want to keep track of which admins reviewed which comments, then you certainly can introduce a relationship between them. However, you don't need a relationship just to give admin users access to all comments. Rather, your application code can check whether the logged-in user is an admin user or not, and depending on their status show different buttons or content.

如果任何管理员可以查看任何评论,则无需对它们之间的关系进行建模。不要将数据建模与系统建模混淆。数据模型只需要为要记录的事实建模。如果您想跟踪哪些管理员审核了哪些评论,那么您当然可以介绍它们之间的关系。但是,您只需要为管理员用户提供访问所有注释的权限就不需要关系。相反,您的应用程序代码可以检查登录用户是否是管理员用户,并根据他们的状态显示不同的按钮或内容。

You ask whether "two tables can only be related if they have primary key / foreign key relationship among them". In the relational and entity-relationship perspectives, we don't relate tables, we use tables to relate sets of values. Some sets represent real-world things (like users) while others represent labels (like names) or measurements (like dates). Any two or more sets of values can be related by creating a suitable table for that purpose. Foreign key constraints are used to indicate that one column (set of values) is a subset of another column (set of values), not to associate rows. For more on this topic, I recommend a book like Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars.

你问“两个表是否只能在它们之间有主键/外键关系时才能相关”。在关系和实体关系视角中,我们不关联表,我们使用表来关联值集。有些集合代表现实世界的东西(如用户),而其他集合代表标签(如名称)或度量(如日期)。通过为此目的创建合适的表,可以关联任何两组或更多组值。外键约束用于指示一列(值集)是另一列(值集)的子集,而不是关联行。有关此主题的更多信息,我推荐了Lex de Haan和Toon Koppelaars等数据库专业应用数学的书。

#2

You seem to have some fundamental misunderstandings about Entity-Relationship models/diagrams and relational modeling.

您似乎对实体 - 关系模型/图表和关系建模存在一些基本的误解。

Entity types/tables have box icons and relationship types/tables have diamond icons. Participations of entities in relationships are the lines/FKs. Your diagram already has a relationship & table between reported comments and admins: reviews. If you want the rows satisfying a different application relationship/association then you either have to express it in terms of given/base relationships via a query or you have to add a new given/base relationship & table.

实体类型/表具有框图标,关系类型/表具有菱形图标。实体在关系中的参与是线/ FK。您的图表已经在报告的评论和管理员之间建立了关系和表:评论。如果您希望行满足不同的应用程序关系/关联,那么您必须通过查询以给定/基本关系表达它,或者您必须添加新的给定/基本关系和表。

-- REPORTEDCOMMENTID identifies reporting of comment COMMENTID ...
select * from ReportedCommentInfo

--admin ADMINID has name NAME and ...
select * from Admin   

--   REPORTEDCOMMENTID identifies reporting of comment COMMENTID ...
-- AND admin ADMINID has name NAME and ...
select * from ReportedCommentInfo join Admin

You don't need lines/FKs to query. They're just given/base participations. (Misrepresentations of ER modeling call participations/FKs "relationships".)

您不需要行/ FK来查询。他们只是给予/基础参与。 (错误陈述ER建模调用参与/ FK“关系”。)

PS There are tables and there are jobs. Tables record the state of the business. A job involves reading and/or updating some relevant tables. An ER diagram just shows the tables. You seem to be confusing ER diagrams with data flow diagrams, which are about how people use tables to do their job. An ER model (schema/diagram) for the data you have in your schema would have User, Admin & Comment entities, a Posts relation on users and comments and a Reports relationship on users and comments, which could be considered a Report associative entity. As to variations, including your schema, you need to pick a particular design method and follow it. Also, you haven't given justification for using the ER diagram you gave.

PS有桌子和工作。表记录了业务状态。工作涉及阅读和/或更新一些相关表格。 ER图只显示了表格。您似乎将ER图与数据流图混淆,数据流图是关于人们如何使用表来完成工作的。您在模式中拥有的数据的ER模型(模式/图表)将包含用户,管理员和评论实体,用户和评论的帖子关系以及用户和评论的报告关系,这些关系可以被视为报告关联实体。至于变体,包括您的模式,您需要选择一种特定的设计方法并遵循它。此外,您还没有给出使用您提供的ER图的理由。

#1

#2