CODEWITHSUNDEEP
sqldatabasedatabase-design
David Reid
David Reid

Reputation: 192

Database Design - Linking two users

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!

Upvotes: 4

Views: 2467

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338211

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.

ERD Entity-Relationship Diagram for table design

Upvotes: 3

Related Questions