Entity Relation Diagram / Database Diagram (MySQL)

Question

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.

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!

Answer 1

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!