Many-to-Many Overhaul

Implementation Recommendations...

1) New syntax for the hasMany definition that allows multiple M2M relationships. Addition of a 'join' attribute that identifies the name of the relationship in the other DO. It would be ideal if 'join' was optional for DOs with only a single M2M relationship.
2) Join table is named based on the singularized concatonation of the two relationship names. The relationship name from the owning DO should be first.
3) FKs should be table + "_" + id.

There is a challange in that many databases limit table and column names to a certan number of characters (I believe in the case of Oracle it's 30).

Example:

class Book {
    static hasMany [authors:[object:Person, join:books],
    publishers:[object:Person, join:publishedBooks]]
    static belongsTo = Person
}

class Person {
    static hasMany [books:[object:Book, join:authors],
    publishedBooks:[object:Book, join:publishers]]
}

This would generate 4 tables. 2 base DO tables (BOOK, PERSON) and 2 join tables (AUTHOR_BOOK, PUBLISHER_PUBLISHED_BOOK). Adding . Let's add some data so that we have the following, one book (book.id= 1) and two authors (person.id = 55 and person.id = 66) and a publisher (person.id = 77).

BOOK

PERSON

AUTHOR_BOOK

PUBLISHER_PUBLISHED_BOOK

The following SQL would then work...

select book.name, person.name as author
from book, person, author_book
where book.id = author_book.book_id
and person.id = author_book.person_id

...resulting in the following...