Database Schema Help

Hey,

I'm trying to figure out a better way to define this relationship: schema

Essentially:

A user can participate in many exhibitions.
An exhibition can have many users.
Users can have roles assigned on a per-exhibition basis.

I'd love to find a nice way to link exhibitions and exhibition_roles for users. Currently they're not linked since there's a many-to-many relationship between users and exhibitions.

mikebronner

11 years ago

Level 16

Maybe something like this:

class User
{
    public function exhibitions()
    {
        return $this->belongsToMany('Exhibition', 'exhibition_role', 'exhibition_id', 'role_id');
    }

    public function roles()
    {
        return $this->belongsToMany('Role', 'exhibition_role', 'user_id', 'role_id');
    }
}

class Exhibition
{
    public function users()
    {
        return $this->belongsToMany('User', 'exhibition_role', 'exhibition_id', 'user_id');
    }

    public function roles()
    {
        return $this->belongsToMany('Role', 'exhibition_role', 'exhibition_id', 'role_id');
    }
}

class ExhibitionRole
{
    public function user()
    {
        return $this->belongsTo('User');
    }

    public function exhibition()
    {
        return $this->belongsTo('Exhibition');
    }

    public function role()
    {
        return $this->belongsTo('Role');
    }
}

class Role
{
    public function users()
    {
        return $this->belongsToMany('User', 'exhibition_role', 'role_id', 'user_id');
    }

    public function exhibitions()
    {
        return $this->belongsToMany('Role', 'exhibition_role', 'exhibition_id', 'role_id');
    }
}

Then create the following migrations (I listed just as pseudo-code):

users table with fields: id, name, etc....
exhibition_role table with fields: id, role_id, exhibition_id, user_id
exhibitions table with fields: id, name, etc....
roles table with fields: id, name, etc....

This may not be spot-on, but it should get your pretty close. :)