This example maps out a many-to-many relationship. It defines two item types, user
and address
. Each user can have many addresses. Many users may live at the same address.
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE gsa-template PUBLIC "-//Art Technology Group, Inc.//DTD Dynamo Security//EN" "http://www.atg.com/dtds/gsa/gsa_1.0.dtd"> <gsa-template> <header> <name>People Repository Version H</name> <author>Pat Durante</author> <description> This template maps out a many-to-many relationship between user items and address items. Each user can have many addresses. Many users may live at the same address. </description> </header> <item-descriptor name="address"> <table name="addr_tbl" type="primary" id-column-names="address_id"> <property name="street" data-type="string"/> <property name="city" data-type="string"/> </table> <table name="user_address_tbl" type="multi" id-column-names="addr_id"> <property name="users" column-names="user_id" data-type="set" component-item-type="user"/> </table> </item-descriptor> <item-descriptor name="user" default="true"> <table name="usr_tbl" type="primary" id-column-names="id"> <property name="id" data-type="string"/> <property name="name" column-names="nam_col" data-type="string"/> <property name="age" column-names="age_col" data-type="int"/> </table> <table name="user_address_tbl" type="multi" id-column-names="user_id"> <property name="addresses" column-names="addr_id" data-type="set" component-item-type="address"/> </table> </item-descriptor> </gsa-template>
SQL Statements
drop table addr_tbl; drop table user_address_tbl; drop table usr_tbl; CREATE TABLE addr_tbl ( address_id VARCHAR(32) not null, street VARCHAR(32) null, city VARCHAR(32) null, primary key(addr_id) ); CREATE TABLE user_address_tbl ( addr_id VARCHAR(32) not null references addr_tbl(address_id), user_id VARCHAR(32) not null references usr_tbl(id), primary key(addr_id, user_id) ); CREATE INDEX user_address_tbl_user_idx ON user_address_tbl(user_id); CREATE TABLE usr_tbl ( id VARCHAR(32) not null, nam_col VARCHAR(32) null, age_col INTEGER null, primary key(id) );