2

In a conference management system, a user can create one ore many conferences. The user that creates a conference is the conference organizer. But a user can also register in a conference.

So there should be 3 entities: Conference, User, Organizer

Conference: id, name, description

Organizer: id, name, description

User: id, name, username, password, email, etc

Now, do you know how the relationships between this entities should be?

This model seems difficult to understand, because a organizer can organize multiple conferences and a conference is organized by one organizer. So should be a 1 to many relationship between organizer and conference? But then, the organizer is also a user that besides create a conference can also register in conferences. So how should be the relation between the user and the event/organizer?

2

First, lets consider the User - Organizer relationship:

  • An Organizer is a User but a User may or may not be an Organizer

This is a one-to-one (1 - 0..1) relationship. One way to implement it is by having the PRIMARY KEY of Organizer being a FOREIGN KEY that references User:

User: 
    user_id         PK
    name
    username
    password 
    email

Organizer: 
    organizer_id    PK  FK -> User 
    description

Now, for the Conference, it seems there are 2 relationships:

  • An Organizer can organize many Conferences; a Conference has exactly one Organizer.

This is one-to-many relationship as you correctly noted. The common way to implement it is to have organizer_id in Conference:

Conference
    conference_id   PK
    organizer_id       FK -> Organizer
    name
    description
  • The relationship between User and Conference is probably many-to-many. A User can participate in many conferences and a conference can have many participating user.

We need a junction table:

Participation
    conference_id   PK FK1 -> Conference
    user_id         PK FK2 -> User
  • Thanks for your answer. I didnt understand this part "This is a 1 - 0..1 relationship", because you have user_id as a fk in the organizer table so like that its not a one to many relationship between user and organizer? – Denn Dec 16 '17 at 0:46
  • 1
    It's one-to-one. The PK of Organizer is also the FK to User. I edited a bit on that part. I hope it's more clear now. – ypercubeᵀᴹ Dec 16 '17 at 0:48
  • Thanks again, so the user table stays with(user_id, name, username,password, email, organizer_id) and the organizer table with (organizer_id, description) right? Also in this case do you know if its a identifying or non identifying relationship? – Denn Dec 16 '17 at 0:54
  • 1
    It's identifying - because you can't have an organizer without (being also) a user. See this answer at SO: stackoverflow.com/questions/762937/… – ypercubeᵀᴹ Dec 17 '17 at 2:25

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.