The one-to-many relationship is the workhorse of SQL data models. It is used in a variety of different ways. The SQL repository supports one-to-many relationships between two tables, and does not interpret the results according to any specific paradigm. This allows your application to apply whatever meaning you want to one-to-many relationships.

The SQL repository implements one to many relationships as multi-valued properties. To implement a multi-valued property:

  1. Set the table type attribute to multi:

    <table name="..." type="multi" ...

  2. Set the multi-column-name attribute of the table tag:

    <table name="..." type="multi" multi-column-name="idx" ...

    Note that the multi-column-name attribute ensures that the ordering of the multi-values are maintained. The column specified by the multi-column-name attribute is used for multi-valued properties of data type array, map, and list and is not used for sets (which are unordered). For map type properties, the values in the column specifiedy by the multi-column-name attribute must be a string. For list or array type properties, these values should be an integer or numeric type, and must be sequential.

  3. The multi-valued property in this table must set its data-type attribute to array, set, maporlist:

    <property name="..." column-name="interest" data-type="array" ...

  4. If the property is a collection of primitive data types such as string, int, and double, specify the data type of the members of the collection by with the component-data-type attribute in the <property> tag for the multi item property:

    <property name="interests" column-name="interest" data-type="array"

    Note that the SQL repository does not support multi-valued collections of binary type members.

  5. If the property is a collection of repository items defined by other item descriptors (for example, an array of users), specify the repository item type of the members of the collection with the component-item-type attribute to the <property> tag for the multi item property:

    <property name="..." column-name="designers" data-type="array"

    The value of the component-item-type attribute is the name of the item descriptor that defines the item type of the members of the collection of repository items.

  6. As with auxiliary tables, the ordering of the ID column names is important. The columns in the id-column-names attribute must be listed in the same order as they are in the id-column-names attribute of the primary table.

  7. You cannot establish a default value for multi-valued attributes.

The following example shows how the XML repository definition might specify the multi-valued attribute interests:

<item-descriptor name="user">
  <table name="dps_user" id-column-names="id" type="primary">
    <property name="login" data-type="string"/>
  <table name="dps_interest" type="multi" id-column-names="id"
    <property name="interests" column-name="interest" data-type="array"

See also the Sample SQL Repository Definition Files section in the SQL Repository Reference chapter for more examples of one-to-many relationships in repository definitions.

loading table of contents...