You can represent many-to-many data relationships in an 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 with 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:
<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
items use the primary database tableauthor
.book
items use the primary database tablebook
.author
andbook
items both use the intermediate multi tableauthor_book
to handle the relationship between authors and books.
The data type of the properties in the intermediate multi table must be Set, not array, map or list.
Tables can have columns other than the ones referenced in the repository definition file, provided two conditions are true:
The columns allow null values.
There is no design requirement that the repository recognize the existence of such columns.