You can also represent many-to-many data relationships in a SQL repository. For example, an author may have written multiple books, and a book may have multiple authors. Representing this kind of relationship depends on the type="multi" attribute in a <table> tag. You can represent a many-to-many relationship using two one-to-many relationships that point to the same intermediate table. The following example represents a many-to-many relationship between the authors of a book and the books written by an author. Author items use a primary database table named author, book items use a primary database table named book, and both author and book items use a multi table named author_book as an intermediate table that handles the relationship between authors and books.
<item-descriptor name="author"> <table type="primary" name="author"> ... </table <table type="multi" name="author_book" id-column-names="author_id"/> <property name="booksWritten" column-name="book_id" data-type="set" component-item-type="book"/> </table> </item-descriptor> <item-descriptor name="book"> <table type="primary" name="book"> ... </table <table type="multi" name="author_book" id-column-names="book_id"/> <property name="authors" column-name="author_id" data-type="set" component-item-type="author"/> </table> </item-descriptor>
This example uses three tables, author, book, and author_book. The data type of the properties in the intermediate multi table must be set, not array, map or list. Note also that tables can have columns other than the ones referenced in the repository definition file, so long as such columns allow null values and so long as there is no design requirement that the repository recognize the existence of such columns.
