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!