引用多个表中的数据 [英] Referencing data from multiple tables

问题描述

我有三个简单的实体:

课程就像一本书，是一种可销售的产品.课程实体代表课程，并具有各种属性，例如持续时间，费用，作者，类型等.

Course is like a Book, a sell-able product. The Course entity represents a course and has various properties, such as Duration, Fees, Author, Type and so on.

Course 
{
    int Id;
    string Title;
}

主题就像一本书中的单个页面，具有实际的学习内容.一个主题可能会出现在多个课程中.

A topic is like an individual page in a Book, it has the actual learning content. A topic may appear in multiple courses.

Topic
{
    int Id;
    string Title;
}

在书中，测验也是一个单独的页面，其中包含问题而不是学习内容.同样，测验可能会出现在多个课程中.

In context of a book, Quiz is also an individual page which holds questions instead of learning content. Again a Quiz may be appear into multiple courses.

Quiz
{
    int Id;
    string Title;
}

现在我有单独的主题和测验，我希望有一个表格将主题和测验组装成一本书.将此表视为书中的目录.以下是我希望它看起来像的轮廓:

Now that i have individual Topics and Quizzes i wish to have a table that will assemble Topics and Quizzes into a Book. Consider this table as Table of Contents in a book. Below is a outline of what i am expecting it to look like :

CourseContents
{
     int CourseId; // Foreign-Key to Courses.Id
     int Page;     // Foreign-Key to either Topic.Id or Quiz.Id
     int SNo;      // Sequence of this page (topic/quiz) in the course, much like page number in a book.
     int Type      // Type of the page i.e, Quiz or Topic. 
}

在RDBMS中有什么方法可以做到这一点?

Is there any way to achieve this in RDBMS ?

我正在寻找的一种方法是创建一个表，以为给定的课程项目创建唯一的标识符.然后在映射表课程-主题"和课程-测验"中使用它.请参考以下内容:

One approach i am looking at is creating a table to create a unique identifier for a given Course Item. Then use it in mapping tables Courses-Topics and Courses-Quizzes. Please refer below :

CourseContents
{
    int Id;        // CourseContentId Primary-Key for this table
    int CourseId;  // Foreign key to Course.Id
    int SNo;       // Serial number of an item in this course;
}

CourseTopics
{
    int TopicId;             // Foreign-Key to Topics.Id
    int CourseContentsId;    // Foreign-Key to CourseContents.Id
}

CourseQuizzes
{
    int QuizId;               // Foreign-Key to Quizzes.Id
    int CourseContentsId;     // Serial number of the quiz in the course
}

问题: CourseContentId表示特定课程中(Topic/Quiz)的特定位置.两个项目不能在一个课程序列中占据相同的位置，因此一个CourseContentId必须仅与CourseTopics或CourseQuizzes中的一个项目相关联.我们如何在两个表之间对CourseContentsId施加唯一约束?

Problem : The CourseContentId represent a particular position ( of Topic/Quiz ) in a particular course. Two items cannot occupy same position in a course sequence, hence one CourseContentId must be associated with just one item in either CourseTopics or CourseQuizzes. How can we put unique constraint on CourseContentsId across two tables ?

进一步添加

可以通过在CourseContents，CourseTopics和CourseQuizzes列中添加ContentType列来解决上述问题.然后在表格上应用检查约束以确保:

The above said problem can be solved by adding a ContentType column in CourseContents, CourseTopics and CourseQuizzes column. Then applying Check constraint on the tables to make sure :

• CourseContents具有CourseContentId和ContentType的唯一组合.
• 课程主题&CourseQuizzes的内容类型必须相同.
• 在CourseTopics&中添加一个引用CourseContents(CourseContentId，ContentType)的外键.CourseQuizzes表.

这将确保CourseContentId不会同时出现在两个表中.

This will ensure that a CourseContentId will not appear in both the tables.

推荐答案

CREATE TABLE CourseContents (
  CourseContentsId INTEGER NOT NULL PRIMARY KEY,
  CourseContentType CHAR(1) CHECK (CourseContentType IN ('T', 'Q')),
  CourseId INTEGER REFERENCES Courses(Id),
  SNo INTEGER NOT NULL,
  CONSTRAINT UniqueCourseContent UNIQUE (CourseId, SNo),
  CONSTRAINT UniqueCourseContentMapping UNIQUE (CourseContentsId, CourseContentType),
);

课程内容表会为每个CourseId和SNo组合生成一个唯一ID(CourseContentsId)，然后可以在Topics&中引用该ID.测验表.由于存在两个不同的表(主题和测验)，因此我们引入了另一列，用于标识与其链接的内容的类型(主题/测验).通过对CourseContentsId&使用复合UNIQUE约束CourseContentType我们确保每个条目只能链接到一种内容类型.

Course Contents table generates a Unique Id ( CourseContentsId ) for each CourseId and SNo combination, which can then be referenced in the Topics & Quizzes table. As there are two different tables (Topics & Quizzes), we introduce another column that identifies the Type of the Content(Topic/Quiz) that it is linked to. By using a composite UNIQUE constraint on CourseContentsId & CourseContentType we make sure that each entry can be linked to only one content type.

CREATE TABLE CourseTopics (
  CourseContentsId INTEGER NOT NULL,
  CourseContentType CHAR(1) DEFAULT 'T' CHECK (CourseContentType = 'T'),
  TopicId INTEGER REFERENCES Topics(Id),
  PRIMARY KEY (CourseContentsId, CourseContentType),
  FOREIGN KEY (CourseContentsId, CourseContentType) REFERENCES CourseContents (CourseContentsId, CourseContentType)
);

Course Topics表是Topics和Courses之间的映射表(在Courses& Topics表之间有多对多的关系).外国人CourseContents表的主键可确保我们为每个CourseContent有一个条目(换句话说，Course& SNo).该表将CourseContentType限制为仅接受"T"，这意味着给定的CourseContentId必须具有主题"的内容类型"才能与主题"链接.

Course Topics table is mapping table between Topics and Courses ( we have many-to-many relationship between Courses & Topics tables ). The foreign & primary key to CourseContents table ensures that we'll have one entry for each CourseContent ( in other words Course & SNo ). The table restricts CourseContentType to only accept 'T', which means a given CourseContentId must have Content Type of Topic inorder to be linked with a Topic.

CREATE TABLE CourseQuizzes (
  CourseContentsId INTEGER NOT NULL,
  CourseContentType CHAR(1) DEFAULT 'Q' CHECK (CourseContentType = 'Q'),
  QuizId INTEGER REFERENCES Quizzes(Id),
  PRIMARY KEY (CourseContentsId, CourseContentType),
  FOREIGN KEY (CourseContentsId, CourseContentType) REFERENCES CourseContents (CourseContentsId, CourseContentType)
);

类似于主题表，我们现在创建CourseQuizzes表.唯一的区别是，这里的CourseContentType为'Q'.

Similar to the Topics table we now create CourseQuizzes table. Only difference is here we have CourseContentType 'Q'.

最后，为了简化查询，我们可以创建一个将这些表连接在一起的视图.例如，下面的视图将列出:CourseId，SNo，ContentType，TopicId，QuizId.在书籍的上下文中，通过此视图，您可以获取给定书籍(课程)的特定页码(SNo)上的内容，页面上的内容类型(主题或测验)和内容的ID.

Finally to simplify querying, we can create a view that joins these tables together. For e.g, the view below will list : CourseId, SNo, ContentType, TopicId, QuizId. In context of a book, with this view you can get what's on a particular Page Number (SNo) of a given book (Course), with type of content on the page (Topic or Quiz) and the id of the content.

CREATE VIEW CourseContents_All AS 
SELECT CourseContents.CourseId, CourseContents.SNo, CourseContents.CourseContentType , CourseTopics.Id, CourseQuizzes.Id
FROM CourseContents
LEFT JOIN CourseTopics ON (CourseContents.CourseContentsId = CourseTopics.CourseContentsId)
LEFT JOIN CourseQuizzes ON (CourseContents.CourseContentsId = CourseQuizzes.CourseContentsId);

这种方法给我带来的好处是:

The advantages that i feel with this approach are :

1. 此结构遵循继承性，这意味着我们可以通过添加另一个表并修改CourseContents表中的CourseContentType Check约束来支持更多内容类型.
2. 对于给定的CourseId和SNo.我也知道内容类型.这肯定会对应用程序代码有所帮助.

注意:检查约束在MySQL中不起作用.为此，需要使用触发器.

Note : Check constraint does not work in MySQL. For it one needs to use Triggers instead.

这篇关于引用多个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！