0

I have the following Entity Relation diagram (MySQL), which I made for a small project(for the university). I would like to know if the relation between PK and FK is properly made.

I am aware that token should not be used in the users table.

enter image description here

Don't know why my brain cannot comprehend the relationships properly. As far as I've managed to come up is:

Many users can have one role. (User, Admin, University)

One role can be belong to many users.

One user can have one event.

One event can belong to one user.

One user can have many attendings.

Many attendings can belong to one user.

Many events can have many attendings. (Although One event can have many attendings?)

Many attendings can belong to many events. (Although many attendings can belong to one event)

I did another diagram using Reverse Engineering from Workbench, and it gave me almost the same. The only difference is that between events and attenders is a one-to-many relationship.

Sometimes I look at them and I am not aware of which question is the best when 2 questions are possible. Is it one too many, or many too many.... This is a tricky question for my brain. It is like there is a missing link that I cannot grasp, and I am pretty sure it is not that hard.

And this is just a simple relationship, don't wanna know the smack my brain would give me if I were to make a 10-20 tables diagram ... Any other criticism is welcomed :) Thank you!

Florea Constantin Cristian
  • 11
  • 3

1 Answers1

0
Many users can have one role. (User, Admin, University)

One role can be belong to many users.


One user can have one event.

One event can belong to one user.


One user can have many attendings.

Many attendings can belong to one user.

All of these statements are correct. From my understanding, you're having difficulty describing the relationship between users and events. Essentially what you have is a many-to-many relationship between users and events. In order for a many-to-many relationship to exist in standard relational systems, there must be a join table. The attenders_to table is serving the purpose of a join column in this relationship.

There also appears to be a separate relationship between events and users, in that each event has a direct column for a userId (I imagine this represents the person hosting/putting on the event). This can be seen as almost independent of the other relationship.

So in short, there are actually two relationships between Users and Events: A Many-to-Many relationship describing the people attending the event, as well as a One-to-Many (that is, one User to many Events) relationship describing the host of the event.

Let me know if this helps clear things up or if there's anything else that I'm missing!

scottmcgowan
  • 7
  • 2
  • Sorry if my question was not explicit enough and I appreciate your answer but the main problem was with events and attenders. Mainly, how do you know which question is the correct one... What is the correct approach: Is it many to many, or one to many? Is it, that one event has many attenders, or many events have many attenders? Or maybe I am viewing everything from a different angle, and I should see it as you mentioned. Maybe I should see it as Users to Events through Attenders rather than just attenders as a different entity from users. Hope it makes sense what I am confused about. – Florea Constantin Cristian May 16 '22 at 18:24
  • Yes, I should have been clearer as well, apologies! I don't think 'attenders' should be seen as a separate entity since they're really just users. That entire table should merely be seen as the join table in the many-to-many relationship between users and events! – scottmcgowan May 16 '22 at 19:47
  • Understood, thank you for taking the time to explain it! I'll try to make more of this diagram just to test the solution. – Florea Constantin Cristian May 17 '22 at 09:19