MySQL表USERS和FLASHCARDS - 如何根据另一个修改一个？ [英] MySQL tables USERS and FLASHCARDS - how to modify one depending on the other?

问题描述

我正在实施一个flashcard应用程序，因此我的FLASHCARDS表包含QUESTION和RESPONSE等字段，而我的USERS表只是我的flashcard应用程序的名称和个人信息。

这是我感到困惑的地方。 FLASHCARDS表将容纳100个问题和响应，分类为抽认卡（或卡组）的组。当USERS使用抽认卡时，他们也可以决定问题是简单，正常还是困难 - 从而修改下一次显示卡片的时间。

<如果我只有一个用户这不会有问题 - 我只是相应地修改了FLASHCARDS表，但我也有100个用户。如何根据USERS对EASY，NORMAL或DIFFICULT的决定修改FLASHCARDS表，并为每个用户记录所有这些（我想象在USERS表中）。

解决方案

Flashcard实体与用户实体之间存在多对多关系。

A特别是用户对特定的抽认卡做出决定。

用户可以对零，一个或多个抽认卡做出决定。

闪卡可以由零，一个或多个用户决定。

这是一个经典的多用户-many relationship。

在关系模型中，我们引入了一个新的关系表，它建立了User和Flashcard之间的关系

作为此表的外观示例：

  CREATE TABLE user_flashcard 
 （user_id INT UNSIGNED NOT NULL COMMENT'fk ref user'
，flashcard_id INT UNSIGNED NOT N ULL COMMENT'fk ref flashcard'
，decision VARCHAR（30）COMMENT'EASY，NORMAL，DIFFICULT'
，PRIMARY KEY（user_id，flashcard）
，CONSTRAINT FK_user_flashcard_user 
 FOREIGN KEY （user_id）REFERENCES user（id）
，CONSTRAINT FK_user_flashcard_flashcard 
 FOREIGN KEY（flashcard_id）REFERENCES flashcard（id）
）
  

您可以添加其他属性，例如：用户上次查看闪卡时的次数，用户查看的次数。

您还需要考虑这是否只是一个纯粹的关系，或者这可能实际上是您模型中的实体。如果我们有重复的属性，或者任何其他实体可能与此表相关，我们可能想要引入一个简单的主键（ id ），其他表可以在外键中引用。

我们也想考虑一下，我们是否希望用户在闪卡上做出多个决定？ （user_id，flashcard_id）是否必须是唯一的，或者它应该是非唯一的。

关键是数据库设计是数据分析。 做数据分析的最佳尝试和实践技术之一是实体关系建模。

---

关注示例示例

为了演示此关系表的工作原理，我创建了一个非常短的SQL小提琴，用一些相当简单的SQL来演示那些可以很容易回答的问题。

SQL Fiddle Here http://sqlfiddle.com/#!9/090ee/5

I'm implementing a flashcard app, hence my FLASHCARDS table contains fields such as QUESTION and RESPONSE, and my USERS table is just that - names and personal info of my flashcard app.

Here's where I get confused. The FLASHCARDS table will hold 100s of questions and responses categorized into "groups" of flashcards (or decks). When the USERS "use" the flashcards they will also be able to decide if the question was EASY, NORMAL, or DIFFICULT - hence modifying the time till the card is next shown.

If I only had one user this wouldn't be a problem - I'd just modify the FLASHCARDS table accordingly, but I´ll also have 100s of users. How can I modify FLASHCARDS table depending on each USERS decision of EASY, NORMAL or DIFFICULT and keep record of all of this for each user (I imagine in USERS table).

解决方案

You've got a many-to-many relationship between the "Flashcard" entity and the "User" entity.

A particular "User" makes a decision about a particular "Flashcard".

A "User" can make a decision on zero, one or more "Flashcard".

A "Flashcard" can be decided by zero, one or more "User".

That's a classic many-to-many relationship.

In the relational model, we introduce a new relationship table, that establishes the relationship between "User" and "Flashcard"

As an example of what this table might look like:

CREATE TABLE user_flashcard
( user_id      INT UNSIGNED NOT NULL COMMENT 'fk ref user'
, flashcard_id INT UNSIGNED NOT NULL COMMENT 'fk ref flashcard'
, decision     VARCHAR(30)           COMMENT 'EASY,NORMAL,DIFFICULT'
, PRIMARY KEY (user_id,flashcard)
, CONSTRAINT FK_user_flashcard_user 
     FOREIGN KEY (user_id) REFERENCES user(id)
, CONSTRAINT FK_user_flashcard_flashcard 
     FOREIGN KEY (flashcard_id) REFERENCES flashcard(id)
)

You could add additional attributes, for example: the last time the user viewed the flashcard, the number of times the user has viewed it.

You also need to consider whether this is just a pure relationship, or if this is might actually be an entity in your model.If we have repeating attributes, or any other entities might be related to this table, we'd likely want to introduce a simple primary key (id) that other tables can reference in a foreign key.

We also want to think about this, do we want a user to have more than one decision on a flashcard? Does (user_id,flashcard_id) need to be unique, or should it be non-unique.

The key to database design is data analysis. And one of the best tried-and-true techniques for "doing" data analysis is Entity Relationship Modeling.

---

FOLLOWUP EXAMPLE DEMONSTRATION

As a demonstration of how this "relationship" table works, I've created a very short SQL Fiddle, demonstrating the kind of questions that can be fairly easily answered using some fairly simple SQL.

SQL Fiddle Here http://sqlfiddle.com/#!9/090ee/5

这篇关于MySQL表USERS和FLASHCARDS - 如何根据另一个修改一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！