有没有办法在E-R图中表示元组与表之间的关系？ [英] Is there a way to represent relationship between a tuple and to a table in E-R diagram?

问题描述

我知道这听起来有些荒谬，但是我怀疑如果我们可以表示两个表之间的关系，两个表的数据如何关联，是否有一种表示关系的方式，例如每个注册用户都有一个单独的关系Pagelikes表。为了更清楚地说明我的疑问，让我以这种方式进行解释，即学生注册了几门课程，例如c，c ++，java或其他课程。然后，我们可以说：

学生-（报名）-课程

这意味着学生x可以注册像c，c ++，java或其他课程可以被1个或更多的学生注册。

同样，我们可以用某种方式来表示这种关系注册用户1有一个为他或她创建的pagelikes_1表？

示例图片：

该用户的页面喜欢] 1

解决方案

TL; DR

您的问题或多或少是一个必须解决的典型情况在DDL（数据定义语言）与 EAV （实体-属性-值）。

如果可以，请使用一张表格

通常，您会有一个表，该表是您正在讨论的所有表的并集。至于报名。

  //学生[学生]已报名[课程] 
报名（学生，课程） 
 PK（学生，课程）
 FK（学生）至学生（学生）
 FK（课程）至课程（课程）
 
 //用户[user]点赞页面[页面] 
点赞（用户，页面）
 PK（用户，页面）
 FK（用户）到用户（用户）
  

没有标准的ER方法来表示每个实体实例的表，即每个实体实例的关联集。这是因为您通常会拥有一个喜欢的页表（例如注册）。

约束用户表以及有关表的表

有描述数据库本身的元数据表。 （详细信息取决于DBMS。）将有一个表，我将其称为Table，其中的一列将称为tablename，每个数据库表均带有一行，并将其名称保留在tablename中。所需的关系（即约束）可以在表和用户表之间表达。 （但不一定在特定的SQL DBMS中以声明方式。）

尚不清楚您要记录的是哪种关系/约束。可能您至少要说的是，如果存在一个像Likes_ user 这样的名称的表，那么它的用户就会存在。这不是ER图显示的那种关系 /约束，它们是FK（外键）。大多数（但不是全部）SQL DBMS不能以声明的方式来表示。 不存在（从表中选择表名，其中表名类似'Likes_％'，但从用户中选择CONCAT（'Likes_'，user）作为用户名）。

您可以计算用户的表名并将其存储在用户列（如表名）中，并声明 FK表（表名）引用用户（表名）。 （如果您的DBMS允许，则将likestablename声明为计算列。）如果您可以向Table添加可为空的用户列，则可以声明FK表（用户）引用用户（用户）。但是您不能，因为它是系统表。您可以声明一个视图，但是SQL不允许您在视图中声明FK。

您还可能希望每个User用户在Table tablename中都有一个表名。那是
不存在（从用户中选择CONCAT（'Likes_'，user）作为表名，除了从Table中选择表名，其中表名如'Likes _％'）。 / p>

如果Table的表名值与User用户的值相同，则可以声明FK User（用户）对Table（表名）的引用。由于它们不同，因此可以使用User tablename技术来声明 FK User（表名）引用Table（表名）。

如果您同时需要这两个约束，那么您希望每个用户的 some 行之间具有正好为1到正1的关系（即 1对1对应）。两个表的表。在大多数SQL DBMS中，即使对于 all 行，以声明方式进行操作也不容易，因为它们不允许在表之间的双向FK。通常，您可以通过在同一表中将它们配对来处理全行情况。 （可能涉及视图。）但是在这里您不能这样做，因为Table是系统表。

如果您的喜欢_ user 表具有不同的列，然后是DDL与EAV

您可能希望每个表中都有不同的列。这样一来，您确实可以拥有由DML和DDL管理的多个Likes_ user 表，这很明显并且可以完全使用DBMS。但是，如果在这种不同的情况下，在测试和成本计算后 ，DDL不足，则可以使用单个表以及多个表和/或空列。这些实现了一种 EAV技术，否则它是一种反模式，因为您失去了许多DBMS功能。

I know this might sound a bit ridiculous, but I got a doubt that if we can represent relationship between two tables, how the two table's data is related, is there a way to represent relationship like, each registered user has a seperate pagelikes table. To make it more clear about my doubt, let me explain in this way, students enrolling for a few courses, say c, c++, java or others. Then, we can say:

Student -(enrols for)- courses

which means that student x can register for courses like c, c++, java or courses can be enrolled by 1 or more students.

In the same way, can we, some how, represent the relationship like registered user 1 has a pagelikes_1 table created for him or her?

sample pics: user profile

pagelikes of that user]1

解决方案

TL;DR
Your question is more or less the canonical situation in which one must decide between DDL (Data Definition Language) vs EAV (Entity-Attribute-Value).

Use one table if you can

Normally, you would have one table which is the union of all the tables you are talking about. As for enrolments.

// student [student] is enrolled in [course]
Enrolment(student,course)
PK (student, course)
FK (student) to Student (student)
FK (course) to Course (course)

// user [user] likes page [page]
Likes(user,page)
PK (user, page)
FK (user) to User (user)

There isn't a standard ER way to indicate a table per entity instance, ie an association set per entity instance. That's because you would normally have one liked page table, as for enrolments.

Constrain the user table and tables about tables

There are "metadata" tables that describe the database itself. (Details depend on the DBMS.) There will be a table that I'll call Table with a column that I'll call tablename with a row for each database table, holding its name in tablename. The "relationship", ie constraint, that you want can be expressed between Table and a User table. (But not necessarily declaratively in a particular SQL DBMS.)

It's not clear exactly what relationship/constraint you want to record. Probably you want to say at least that if a table with a name like Likes_user exists then its user does. That is not a "relationship"/constraint of the kind that ER diagrams display, which are FKs (foreign keys). That cannot be expressed declaratively by most (but not all) SQL DBMSs. It is NOT EXISTS(select tablename from Table where tablename like 'Likes_%' except select CONCAT('Likes_', user) as tablename from User).

You could calculate and store a user's tablename in a User column likestablename and declare a FK Table (tablename) references User (likestablename). (Declare likestablename as a computed column, if your DBMS allows it.) If you could add a nullable user column to Table then you could declare 'FK Table (user) references User (user). But you can't because it's a system table. You could declare a view but SQL won't let you declare FKs in views.

You may also want every User user to have a tablename in Table tablename. That is NOT EXISTS(select CONCAT('Likes_', user) as tablename from User except select tablename from Table where tablename like 'Likes_%').

If Table tablename values were just like the User user values then you could instead declare a FK User (user) references Table (tablename). Since they differ, you can use the User tablename technique to just declare a FK User (tablename) references Table (tablename).

If you want both of these constraints then you want every user to have an exactly-1-to-exactly-1 (ie "1-to-1 correspondence") between some rows of two tables tables. That is not easy to do declaratively in most SQL DBMSs even for all rows because they won't allow FKs in both directions between tables. Normally you could handle the all-rows case by pairing them up in the same table. (Possibly involving views.) But here you can't because Table is a system table.

If your Likes_user tables have different columns then it's DDL vs EAV

You might want different columns in each of the tables. Then indeed you could have multiple Likes_user tables managed by DML and DDL, which is clear and uses the DBMS fully. But if in this different-columns case after testing and costing the DDL is inadequate you could use a single table along with multiple tables and/or null columns. These implement a technique "EAV" that is otherwise an anti-pattern since you lose a lot of DBMS functionality.

这篇关于有没有办法在E-R图中表示元组与表之间的关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！