Another data model you can use in the SQL Repository is an ordered one-to-many relationship. Suppose you have an author item descriptor and you want to model each author’s books in the order they were published. Your SQL repository definition file can define two item descriptors that look something like this:
<item-descriptor name="author"> <table name="author" type="primary" id-column-name="author_id"> </table> <table name="book" type="multi" id-column-name="author_id" multi-column-name="sequence_num"> <property name="books" data-type="list" component-item-type="book" column-name="book_id"/> </table> </item-descriptor> <item-descriptor name="book"> <table name="book" type="primary" id-column-name="book_id"> <property name="author" item-type="author" column-name="author_id"/> <property name="seq" data-type="int" column-name="sequence_num"/> </table> </item-descriptor>
Note some limitations for this data model:
You must use the
List
data type to represent the ordered “many” side of the relationship.The
sequence_num
andauthor_id
columns in thebook
table cannot be specified as not null, as the SQL Repository tries to set these fields to null when items in the List are removed.The
book
item descriptor needs to define a property to point to thesequence_num
field, like this:<property name="seq" data-type="int" column-name="sequence_num"/>
SQL Statements
CREATE TABLE author ( author_id VARCHAR(32) not null, primary key(author_id) ); CREATE TABLE book ( book_id VARCHAR(32) not null, sequence_num INTEGER, author_id VARCHAR(32) references author(author_id), primary key(book_id) );