1

I have this setup as my database structure which already works pretty well, but I feel like it could be better, but cannot figure out how.

Database Structure:-
database structure

Events can have multiple sub-events. Users can join these events and optionally their sub-events. With this structure I have all constraints, but the one that links the event attending with the sub event attendings => there can be sub event attendings left without having the main event attending (this shouldn't be possible as users who don't attend the main event cannot attend it's sub events).

I am currently working with Laravel so it looks like this:

User:

  • hasMany Event (as organizer of these events)
  • belongsToMany EventAttending (which events is a user joining)
  • belongsToMany SubEventAttending (which sub-events is a user joining)

Event:

  • belongsTo User
  • hasMany SubEvent
  • belongsToMany EventAttending (which users are attending this event)

SubEvent:

  • belongsTo Event
  • belongsToMany SubEventAttending (which users are attending this sub-event)

The problem arises when trying to manage the sub-event attendings of a user. How would I make this constraint between the event and sub-event attendings exist, while keeping it clean with the capabilities of Laravel / What can you give as advice for a better structuring / How would you guys do it?

Edit

To clarify, the events and sub-events store different things, have different properties. Also I need to store information for the event attendings (like did he attend in real life) and sub-event attendings, most likely differing ones.

Edit

Edward Haber has the better structure, but as my two pivot tables (the connection between User-Event and User-SubEvent) store additional diferent type of information, currently chose to remain with the initial design.

One of the biggest problems I am facing (which does exist with both structure) is querying for a User while getting the attended Events with the attended SubEvents. Trying to achive this result: 

User[]:{  
  ...,  
    attending_events[]:{  
      ...,  
       attending_sub_events[]:{  
         ...  
       }  
    }  
}

Been thinking for hours for a clean solution, couldn't get anything else to work. Wouldn't like to write the whole SQL query manually. My current implementation with the two pivot table looks like this (result not nested, code messy):

$users = User::withCount(['attendingEvents' => function($query) use($eventId){
            $query->where('event_id', $eventId);
        }])
        ->with(['attendingSubEvents' => function($query) use($eventId){
            $query->select('id')->whereHas('event', function($query) use($eventId){
                $query->where('id', $eventId);
            });
        }]);

With this approach I get the sub-events separated from the main event. (querying only for the main attending-event count, because I only need to determine whether he is joining or not).

Fodor Zoltán
  • 51
  • 4
  • 1
    I would argue you really only have 3 tables: `users`, `events` (subevents are really just events without an `organizer`) and `events_users` (users who are attending events/sub-events. – Benny Hill Aug 10 '16 at 01:19
  • I agree with Benny. With this case, seems like the only diff between events and subevents would be organizer_id and event_id. `event: id, organizer_id, event_id` and `event_user: event_id, user_id` Subevent Query: `SELECT * FROM event WHERE event_id is NOT NULL` etc –  Aug 10 '16 at 02:04
  • The sub-events have different type of fields than the events. Basically I need to store different things for each, so can't really merge them .. won't be practical, I think. – Fodor Zoltán Aug 11 '16 at 12:53

1 Answers1

1

The pattern for this type of solution is to use a Polymorphic Relationship. This solution focuses just on connecting the User to the Event and SubEvent - the rest of the relations look correct.

<?php
class User extends Model {
    public function attending() {
        return $this->morphTo();
    }
}

<?php 
class Event extends Model {
    public function attendees() {
        return $this->morphMany('App\User', 'attending')
    }
}


<?php 
class Subevent extends Model {
    public function attendees() {
        return $this->morphMany('App\User', 'attending')
    }
}

The users table needs these fields:

*users*
-----
id
attending_id
attending_type

This is all untested - but the basic idea. (Edited: changed name of polymorphic method to attending from attendee)

Edward Haber
  • 151
  • 3
  • This seems promising, I have to look into it. I forgot to mention that for the two different attendings I need to store additional information, could you suggest a clean way with this implementation (Updated the main post about this.) – Fodor Zoltán Aug 11 '16 at 13:00
  • Yes this is exactly the point of the polymorphic behavior. So that subvent and event can have different functionality depending on which is connected to the User object. In a true polymorphic situation, the classes would share a common interface and a common method call could be performed on each. But you could also abstract that problem and have them not share an interface. But yes, this example assumes that they Event and Subevent cannot be combined and are different tables. – Edward Haber Aug 11 '16 at 18:40
  • I understand this, but I was talking about the pivot table. For someone attending the main event i need to store X type information in the **pivot** table, and for it attending a subevent I need to store Y type information in the **pivot**. Another question is how could I query for a user while getting whether he is joinong a specific event(by event id) and if the case it's subevents he is joining. – Fodor Zoltán Aug 11 '16 at 20:09
  • that users table structure should be the pivot table - my mistake. attending_id is the id of the object (Either Event or Subevent) and attending_type is the full name of the eloquent model including namespace (Either Event or Subevent). Also check out morphMany(). – Edward Haber Aug 12 '16 at 20:08
  • Yeah I figured that you misswrote that. However this structure comes with the same problem I am facing, check updated question. – Fodor Zoltán Aug 14 '16 at 11:07