Establishing relationships through a common table

Question

I have three tables inside of a database: interest, subscription and subscriber. The schema looks like this:

The subscription table has a one to many relationship on both interest and subscriber. How I would like it to work, and I'm not sure if how I want it to work and how it's actually working are lining up, but it should be like this: a Subscriber can have multiple Interests, and each Interest can have multiple Subscribers. This will allow me to see which interests a subscriber has, and which interests have been subscribed to. Then, the Subscription relates only to one subscriber and one interest. I think that's how it's set up, but I just did some tests querying the subscription table and I got this back:

C:\xampp\htdocs\www\public_html\playground\subscribr>php list_subscriptions.php
[e-mail]
--Magazine
[e-mail]
--Newsletter
[e-mail]
--Promotions
[e-mail]
--Email
[e-mail]
--Magazine
[e-mail]
--Promotions
[e-mail]
--Newsletter
[e-mail]
--Email
[e-mail]
--Magazine
[e-mail]
--Promotions
[e-mail]
--Email
[e-mail]
--Magazine
[e-mail]
--Newsletter
[e-mail]
--Promotions
[e-mail]
--Promotions
[e-mail]
--Newsletter 

I suppose I expected a result more like this:

C:\xampp\htdocs\www\public_html\playground\subscribr>php list_subscriptions.php
[e-mail]
--Magazine
--Newsletter
--Promotions
--Email
[e-mail]
--Magazine
--Promotions
[e-mail]
--Newsletter
--Email
[e-mail]
--Magazine
--Promotions
--Email
[e-mail]
--Magazine
[e-mail]
--Newsletter
[e-mail]
--Promotions
[e-mail]
--Promotions
[e-mail]
--Newsletter

I do get this result if I query by subscriber, and grab their interests (through the subscription table). I'm using ORM, so the code to do that looks like this:

$subscriberRepo = $entityManager->getRepository( 'Subscribr\Entity\Subscriber' );
$subscribers = $subscriberRepo->findAll();

foreach ($subscribers as $subscriber) {
    echo sprintf( "-%s\n", $subscriber->getEmail() );
    foreach ($subscriber->getInterests() as $interest) {
        echo sprintf( "--%s\n", $interest->getInterest()->getName() );
    }
}

So I can get a subscriber's subscriptions, and interests that are subscribed to, using this schema, but then is there a point in keeping the subscription table around? Or does the whole thing need to be reworked to do what I'd like it to do? I like the having an indirect relationship between subscriber and interests, incase I wanted to add extra columns to the subscription table like removing is_subscribed from the subscriber table and adding a column in subscription called subscription_status, in that way this solution feels the cleanest. However, it also almost feels like a join table with extra fields. Thoughts?

Answer 1

Based on your explanation a could understand properly what you need to map. In a simplified way you have two options:

Option 1

Subscriber 1 -> n Subscription
Subscription n <- 1 Interest

It means indirectly: Subscriber n -> n Interest
Don't see? Lets code to see better:

foreach ($subscribers as $subscriber) {
   echo sprintf( "-%s\n", $subscriber->getEmail() );
   foreach ($subscriber->getSubscriptions() as $subscription) {
      echo sprintf( "--%s\n", $subscription->getInterest()->getName() );
   }
}

EDIT

The above code will work with the below changes in your mapping. This code explanation: Iterate subscribers where each subscriber has a list of subscriptions and each subscription referes on interest.

Subscriber.php

 /**
  * @var array
  * 
  * @ORM\OneToMany(targetEntity="Subscription", mappedBy="subscriber",    cascade={"persist", "remove"}, orphanRemoval=TRUE)
  */
  private $subscriptions;

You should remove private $interests; from Subscriber. It won't access Interest directly.

Subscription.php

/**
 * @var Interest
 *
 * @ORM\ManyToOne(targetEntity="Interest", inversedBy="subscribers")
 * @ORM\JoinColumn(name="interest_id", referencedColumnName="id", nullable=FALSE)
 */
private $interest;

Notice each Subscription will access a single Interest.

This first option is the best in your case.

Option 2

You could remove Subscription class and use its table as a reference table. And map directly: Subscriber n -> n Interest.

class Subscriber {

     /**
      * @var array
      * 
      * @ORM\ManyToMany(targetEntity="Interest", cascade={"persist", "remove"}, orphanRemoval=TRUE)
      * @ORM\JoinTable(name="subscription",
      *      joinColumns={@ORM\JoinColumn(name="subscriber_id", referencedColumnName="id")},
      *      inverseJoinColumns={@ORM\JoinColumn(name="interest_id", referencedColumnName="id")}
      *      )
      */
      private $interests;
}

The issue in this option you cannot use subscription.interest_date 'cause subscription table is now only a relation table. More explanation about that in this question.