How to write the file yml many-to-many table with 3 PrimaryKey

Question

if our situation is the standard view where you have two tables and a ternary, and easily manageable by following the following documentation

But if we have three instead of two tables and then the ternary consists of 3 PrimaryKey, as I write my yml files?

Example to situation:

Take the case that I have a user who participates in a course. So I have a users table, courses table and a users_has_courses (useri_id, course_id). This is the standard case many-to-many. But I also have a table invoices and therefore a table users_courses_invoices where there are three primaykey (user_id, course_id, invoice_id).

Answer 1

In your situation you have the User and the Course model and they are linked with a many to many relation which has the pair (user_id, course_id) as key. I would call this model subscription, and give to that its own identifier, and then I will link this model with the Invoice model, so your final scheme (minimal version) could be:

User:
  columns:
    id:
      type: integer
      primary: true

Course:
  columns:
    id:
      type: integer
      primary: true

Subscription:
  columns:
    id:
      type: integer
      primary: true
    user_id:
      type: integer
      notnull: true
    course_id:
      type: integer
      notnull: true
  relations:
    User:
      foreignAlias: Subscriptions
    Course:
      foreignAlias: Subscriptions

Invoice:
  columns:
    id:
      type: integer
      primary: true
    subscription_id:
      type: integer
      notnull: true
  relations:
    Subscription:
      foreignAlias: Subscription
      foreignType: One

In this way you have a normalized database and you can access invoices both from users and from courses with this code:

$user->Subscriptions->getFirst()->Invoice
$course->Subscriptions->getFirst()->Invoice

If you want all invoices for a given users you can do a query in this way

InvoiceTable::getInstance()->createQuery('i')
    ->select('i.*')
    ->leftJoin('i.Subscription s')->leftJoin('s.User u')
    ->where('u.id = ?', $user_id);

The same apply if you want all the invoices for a given course.