This section includes a number of simple examples of SQL repository definition files and the corresponding SQL statements to create the tables described by the definition files. These examples demonstrate a variety of data relationship mappings:
Simple One-to-One maps a repository item to a single table row. It includes just a primary table, with no joins with other tables. This is the simplest case.
One-to-One with Auxiliary Table maps a repository item to a primary table and an auxiliary table (a one-to-one relationship). Each user has a job title and function.
One-to-Many with an Array maps a repository item to a primary table and a multi-value table with an array property. This demonstrates a one-to-many relationship. The
multi
table, namedsubjects_tbl
, contains a list of a user’s favorite subjects (simple strings). When using anarray
orlist
type property, themulti
table requires amulti-column-name
attribute (in this example,seq_num
) to ensure that the ordering of the multi-values are maintained.One-to-Many with a Set maps a repository item to a primary table and a multi-value table with a set type property. This is another example of one-to-many relationship. Because a set is used, a
multi-column-name
attribute is not required.One-to-Many with a Map maps a repository item to a primary table and a multi-value table with a map property. When using a
map
type property, themulti
table requires amulti-column-name
attribute (in this example,card_key
). This column contains keys that uniquely identify each of the multi-values. For example, each user has many credit cards; the keys are strings that identify each of the user’s cards (like business card, frequent flyer card, personal card.One-to-Many Mapping to Other Repository Items maps a one-to-many relationship. It defines two item types,
user
andaddress
. Each user can have many addresses.Ordered One-to-Many demonstrates an ordered one-to-many relationship with a
list
type property. It defines two item types,author
andbook
. Each author can have many books, and the order of the books is considered significant.Many-to-Many maps a many-to-many relationship. It defines two item types,
user
andaddress
. Each user can have many addresses. Many users may live at the same address.Multi-Column Repository IDs demonstrates the use of composite repository IDs.