Defining a relationship in Doctrine

I am experimenting with designing a model for a project I am working on in Doctrine, and I wanted some advice on how I would define different relationships.

I've got 3 tables

Venue, Series and Event

• Venue has many events, and can host events that are sometimes but not necessarily part of a series.
• Series is a set of events, which can always occur at the same venue or different venues.
• Event always has to occur at a venue and can optionally be part of a series.

My understanding is that Venue and Series would be a many-to-many relationship as开发者_JS百科 many venues can have many series of events and vice versa. Does the fact that this relationship is "optional" change things?

I also understand that event would be a one to many relationship with both Venue and Series as one Venue can have many events and one Series can have many events, but an event can only have one of each.

Am I looking at this the correct way, and can anyone advise on the best way to define this within Doctrine?

Can you tell me why you recommend a many-to-many relationship between Series and Events. I get that this is because it is an 'optional' relationship, but I'm still a little confused as to why this makes it more flexible.

Actually, i got confused! Ive been working with mixed forms of inheritance a lot lately where this makes sense because of the way things need to be structured and maximizing the use of referential integrity . You could just as easily do the following i think (which is more inline with the requirements of the model as you stated it):

Event:
  columns:
    name: string(255)
    venue_id: integer
    series_id: {type: integer, default: null}
  relations:
    Venue:
      local: venue_id
      type:  one
      alias: Venue
      foreign: id
      foreignType: many
      foreignAlias: Events
      onDelete: CASCADE
    Series:
      local: series_id
      type: one
      alias: Series
      foreign: id
      foreignType: many
      foreignAlias: Events
      owningSide: true
      onDelete: SET NULL         

Venue:
 columns:
   name: string(255)
   # other cols

Series:
  columns:
    name: string(255)

Still though, using a m-m allows you to add specific data to the refClass or to use multiple reference classes for essentially the same relationship (this is where it was handy for me to do it this way in certain situations). So technically its more flexible, but admittedly if what you stated were your only requirements, you'll never need this flexibility :-)

Also, is it the fact that this is a many-to-many relationship that requires the extra SeriesEvent refClass to be defined?

Yes.

To build on from that, is it necessary to define this additional join whenever a many-to-many occurs?

No doctrine will handle the querting automatically anytime you call an accessor for the collection. However in cases of both 1-m and m-m you will often want to add a join to the query so that everytime you call an accessor it doesnt query the db again. For example:

$events = Doctrine_Core::getTable('Event')->createQuery('e')->execute();
foreach($events as $event){

  // each of the following will query the db so you have  
  // 2*(number of events) queries being issued to the DB in this loop

  $series = $event->getSeries(); // this will join through the ref class automatically
  $venue = $event->getVenue();
}

$events = Doctrine_Core::getTable('Event')->createQuery('e')
  ->leftJoin('e.Series s) // this will join with the ref class automatically
  ->leftJoin(e.Venue v)
  ->execute();

foreach($events as $event){

  // because you explicitly joined the realtion all the data 
  // fetched at once, so this entire loop only uses 1 query.
  $series = $event->getSeries();
  $venue = $event->getVenue();
}

I wouldnt relate Series and Venue at all because the relationship will always depend on an Event thus the correlation is not necessary. You can just add your own custom accessors/mutators/finders to your classes to query for the tertiary relationship or pull it from the proper related objects. Additionally because the relation between Event and Series is optional i would use a many-to-many because it allows for the most flexibility.

Something like the following:

Event:
  columns:
    name: string(255)
    venue_id: integer
  relations:
    Venue:
      local: venue_id
      type:  one
      alias: Venue
      foreign: id
      foreignType: many
      foreignAlias: Events
      onDelete: CASCADE
    Series:
      local: event_id
      alias: Series
      refClass: SeriesEvent


Venue:
 columns:
   name: string(255)
   # other cols

Series:
  columns:
    name: string(255)
  relations:
    Events:
      local: series_id
      alias: Events
      refClass: SeriesEvents

SeriesEvent:
  columns:
    event_id: {type: integer, primary: true}
    series_id: {type: integer, primary: true}
  relations:
    Series:
      local: series_id
      type: one
      alias: Series
      foreign: id
      foreignType: many
      foreignAlias: SeriesEvents
      onDelete: CASCADE
    Event:
      local: event_id
      type: one
      alias: Event
      foreign: id
      foreignType: many
      foreignAlias: SeriesEvent
      onDelete: CASCADE