数据库设计 - 链接两个用户 [英] Database Design - Linking two users

问题描述

我需要一些数据库设计的帮助。我是一个有限的开发商贸易，只有处理非常基本的数据库。我只是开始分支到更多的高级网络应用程序，并希望一些指针在正确的方向为架构。

我要找的是一个帐户系统，基本上可以链接两个帐户。

用户以常规方式注册，只是提供姓名，电子邮件，密码以简化这个问题。在他们注册后，用户可以通过输入其他电子邮件并让其他用户接受其他用户，将他们的帐户链接到另一个用户。

一旦此链接创建，两个用户可以一起CRUD任务。

我努力的位是如何创建两个用户之间的链接。我明显有我的用户表。

  USERS：
 id 
 name 
 email 
 password 
  

现在，我相信我需要创建另一个表来保存两个链接的帐户有自己的唯一ID，我们可以使用CRUD任务。像：

  LINKED_USERS：
 id 
 user1id 
 user2id 
 verified 
 
 TASKS 
 id 
 lu_id（FK，Linked_Users id）
 //任何其他字段在这里合并。 
  

这是正确的吗？如果是这样，我将如何设置users表和linked_users表之间的关系？这是令人困惑的位，因为我需要引用两个用户ID的关系。说我想显示user1id和user2id名称，这种关系如何工作？

我希望这是有道理的，如果你需要更多的信息，我只需要编辑这个问题。

感谢您提供任何协助！

解决方案

关于要求。我的设计假设有以下要求：

• 人们成对链接在一起


• 零个，一个或多个任务记录。


• 每个人都可以分配到零个，一个或多个对。

我认为你的困惑围绕着配对。反而认为它是团队。事实上，一个团队最多只能有两个人是不可能的; 2，10，100不重要，因为任何数字以相同的方式处理。这样就是一个分配了成员的团队表。每个人可以属于一个或多个团队，并且每个团队可以具有一个或多个成员。这意味着我们有多对多关系人和团队。多对多是关系设计中的一个问题，它总是通过添加第三个中间或桥表来解决。在这种情况下，该桥表是 membership _ 。

每个团队都拥有零个，每个任务由一个且只有一个团队拥有。这是小组与任务之间的简单一对多关系。 / p>

如果这些假设和约束是正确的，那么您将在关系数据库，例如 Postgres 。

我添加了 start _ 和 stop _ membership _ 以显示人们可能有过去，现在或未来分配给小组的想法。

I need some help with some database design. I am a FE developer by trade and have only dealt with very basic DBs. I am just starting to branch out into more "advanced" web apps and would like some pointers in the right direction for the schema.

What I am looking for is an account system that can basically link two accounts. I will give you the scenario I had imagined off the top of my head.

A user signs up in a regular way, just providing name, email, password for simplicity of this question. After they have signed up, the user can then link their account to another user by entering the others email and having it accepted by the other user.

Once this link has been created, the two users can CRUD tasks together.

The bit I am struggling with is how to create the link between the two users. I obviously have my users table.

USERS:
 id
 name
 email
 password

Now, I believe I need to create another table that holds the two linked accounts, that has its own unique ID that we can use to CRUD tasks. Something like:

LINKED_USERS:
 id
 user1id
 user2id
 verified

TASKS
 id
 lu_id (FK, Linked_Users id)
 // Any other fields for the two combined here.

Is this correct? If so, how would I setup the relationships between the users table and the linked_users table? This is the bit that is confusing me because I need the relationship to reference two users IDs. Say I wanted to display user1id and user2id names, how would the relationship work? Just really need a bit of help wrapping my head around this.

I hope this makes sense, if you need any more information I will just edit the question.

Thanks for any help in advance!

解决方案

Your question in not entirely clear as to the requirements. My design assumes the following about requirements:

• People are linked together in pairs
• Each pair owns zero, one, or more task records.
• Each person can be assigned to zero, one, or more pairs. If not currently, then perhaps over time (past pairs, current pairs, future pairs).

I think your confusion revolves around the pairing. Instead think of it as teams. The fact that a team can have at most two people is beside the point; 2, 10, 100 does not matter because any number is handled the same way. That way is a Team table that has members assigned. Each person can belong to one or more teams, and each team can have one or more members. That means we have a Many-To-Many relationship between Person and Team. A many-to-many is a problem in relational design that is always solved by adding a third intermediate or "bridge" table. In this case, that bridge table is membership_.

Each team owns zero, one, or more tasks. Each task is owned by one and only one team. This is a simple One-To-Many relationship between Team and Task.

If these assumptions and constraints are correct, then you would have the following table design in a relational database such as Postgres.

I added a start_ and stop_ pair of fields on membership_ to show the idea that people may have past, present, or future assignments to teams.

这篇关于数据库设计 - 链接两个用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！