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:

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)
);