需要建议改变我的数据库设计 [英] Need advice to change my database design

问题描述

我需要改变我在DB中存储信息的方式。因为我开发的旧模型的查询工作缓慢。

一般问题如下。

1）我有课程列表，每个课程都有描述课程一般内容的标签列表。例如，名为数据库管理系统的课程可以具有以下标签{ sql，index，key，relation }。

2）我有教授谁有标签也通常描述他们在课程中教什么。例如，Barton {sql，php，apache，mysql}

我需要找到最符合具体选定课程的DB中的所有教授。另外我还需要按照匹配的重量进行排序。

问题

问题是如何将此信息存储在数据库中，以及如何处理此存储的信息以解决此问题。

这个问题出现在我收到了很多关于我的sql查询的消极批评之后，这个问题出现在一个这里。

I need to change the way I am storing information in the DB. Because the query works slow with the old model I had developed.
General problem is following.
1) I have list of courses, and each course has list of tags describing general content of the course. For instance, the course called "Database Management Systems" could have following tags {sql, index, key, relation}.
2) I have professors who have tags also which generally describe what do they teach in their courses . For example, Barton {sql, php, apache,mysql}
I need to find all professors in the DB who match best to the specific selected course. Also I need to sort them by their weight of matching.
Question
The question is how to store this information in the DB and how to process this stored information in order to solve this problem.
This question appeared after I received a lot of negative critiques about my sql query here.

推荐答案

嗯，我会开始有这样的5个表：

Well, I would start with something like these 5 tables:

Course (CourseID, CourseName, ...)
Professor (ProfID, ProfName, ...)
Tag (TagID, TagName)
CourseTag (CourseID, TagID)
ProfTag (ProfID, TagID)

并查询它像

SELECT ProfName, Count(PT.TagID) AS Weighting
FROM Professor P
INNER JOIN ProfTag PT ON P.ProfID = PT.ProfID
INNER JOIN CourseTag CT ON PT.TagID = CT.TagID
WHERE CT.CourseID = @SelectedCourse
GROUP BY ProfName

这是MS SQL Server语法... don'不知道你在用什么（但是用php，可能不是这样：））

That's MS SQL Server syntax...don't know what you're using (but with php, probably not that :))

这篇关于需要建议改变我的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！