I'm studying about db by doing some modeling exercises. But I've two doubts in a exercise.

In the exercise:

A Event can have only one organizer, and the organizer is a user, and a user can organize and participate in many events.

So this is a one to many relationship between organizer and event. My doubt is about the need to create a third table for the relation, because it's just necessary to have a third table in a many to many relationship, right?

But for example, in this case the event organizer has some attributes that don't belong to the user like "details", so we need to create a third table for the event organizer, but this table is not a relationship table it's just a normal entity table right?

But to do the relation, that a user, that is an organizer can organize multiple events and an event is organized by a single organizer, that is a user, how should we do this? Is the below correct? Because the "event_organizer" table seems a relationship table but maybe it should be a entity table.

enter image description here

The other doubt is that an event can have a maximum of 3 categories, and one category can have many events. So it is a many to many relationship? Or because an event can have a maximum of 3 categories is not and it needed a different approach?

  • Are you sure it's the organizer who has details, not the event? Logically, that would make more sense.
    – RDFozz
    Nov 17 '17 at 22:25
  • The event also has details. I didnt put all columns to be more simple. But the event organizer also have details or a description.
    – Jake
    Nov 17 '17 at 22:27
  • The real point I'm trying to get at: You only need an Organizer table if there are values that are not unique to the user or to the event. If these details hold for the user for every event they're organizing, then they should be in the user table. If they're accurate for a specific event, then they should be in the event table. If they're true for a group of events, then they less represent an organizer than a structure where one event has multiple times and dates, maybe even places where it's being held. In that case organizer is actually event_group (better defines purpose).
    – RDFozz
    Nov 18 '17 at 0:40
  • You might get some ideas from here!
    – Vérace
    Nov 19 '17 at 18:35

I'm not familiar with the layout of your database design (there are many layouts in the market). But what I'm figuring out is that you are trying to connect two entities together, directly. Which is wrong. Two entities can only be connected by a relationship (no matter what ER layout you are using).

Your first query can be subdivided into three parts: 1) An event can have only one organizer 2) Organizer is an instance of User 3) There is a one to many relationship between event and organizer.

So you have three entities: Event, Organizer, User. Then, if you have studied about weak entities, you would want to convince yourself that organizer is a weak entity which is dependent on the entity User. Connect entities User, and Organizer with a relationship "is_instance". Now you are done with the entity User.

Let's now move on to the relationship between entities Organizer, and Event. You already figured out that it has a one to many relationship from Event to Organizer. So great!!

Now coming to your next query: An event can have at most three categories but a category can have any number of events. You can say that this is a kind of many to many relationship. Some ERD allow you to explicitly specify the cardinality between the entity and the relationship (as far as I remember it is done by specifying the number over the arrow joining the entity and relationship). So you can model this as joining the entities Event, and Category by a relationship has_category and specify the range 1..3 over the line joining Event and has_category.

Extra clarification: There is always a many to one relationship from a weak entity towards its underlying entity. Your inclusion of details to the entity organizer and the dependence of Organizer's ID on User's ID makes it a separate entity.

(Note: due to lack of information, I assumed that there may be more than one value for the attribute "detail" for the same user in the entity Organizer. If this is not so and that there is only one tuple in the entity Organizer for every ID such that every tuple in the entity Organizer is unique on attribute Organizer_id, then you could also choose to make the Organizer entity as a strong entity and have a total participation of entity Organizer in the relationship between User and Organizer)

Additionally, your query also states that the user can participate in many events, so there can be a many to many relationship between entities User, and Events.

Hope this helps.

  • Thanks, but so what is the relation between the user and the organiser? Is 1 to 1? And is a identifying and non identifying relation?
    – Jake
    Nov 17 '17 at 21:21
  • Added extra clarification to the above answer.
    – abr_78
    Nov 17 '17 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.