--
--  Each row in acs_rel_types represents a type of
--  relationship between objects. For example, the following DML
--  statement:
--  
--  insert into acs_rel_types
--   (rel_type,
--    object_type_one, role_one, min_n_rels_one, max_n_rels_one,
--    object_type_two, role_two, min_n_rels_two, max_n_rels_two)
--  values
--   ('employment',
--    'person', 'employee', 0, null,
--    'company', 'employer', 0, null)
--  
--  defines an "employment" relationship type that can be expressed in
--  in natural language as:
--  

--  A person may be the employee of zero or more companies, and a company
--  may be the employer of zero or more people.
--  
-- 
CREATE TABLE acs_rel_types (
	rel_type	 varchar(100) PRIMARY KEY NOT NULL,
	object_type_one	 varchar(100) NOT NULL,
	role_one	 varchar(100),
	min_n_rels_one	 integer DEFAULT 0 NOT NULL,
	max_n_rels_one	 integer,
	object_type_two	 varchar(100) NOT NULL,
	role_two	 varchar(100),
	min_n_rels_two	 integer DEFAULT 0 NOT NULL,
	max_n_rels_two	 integer,
	CONSTRAINT acs_rel_types_max_n_1_ck
	CHECK (max_n_rels_one >= 0),
	CONSTRAINT acs_rel_types_max_n_2_ck
	CHECK (max_n_rels_two >= 0),
	CONSTRAINT acs_rel_types_min_n_1_ck
	CHECK (min_n_rels_one >= 0),
	CONSTRAINT acs_rel_types_min_n_2_ck
	CHECK (min_n_rels_two >= 0),
	CONSTRAINT acs_rel_types_n_rels_one_ck
	CHECK (min_n_rels_one <= max_n_rels_one),
	CONSTRAINT acs_rel_types_n_rels_two_ck
	CHECK (min_n_rels_two <= max_n_rels_two),
	CONSTRAINT acs_rel_types_obj_type_1_fk REFERENCES acs_object_types (),
	CONSTRAINT acs_rel_types_obj_type_2_fk REFERENCES acs_object_types (),
	CONSTRAINT acs_rel_types_rel_type_fk REFERENCES acs_object_types (),
	CONSTRAINT acs_rel_types_role_one_fk REFERENCES acs_rel_roles (),
	CONSTRAINT acs_rel_types_role_two_fk REFERENCES acs_rel_roles ()
);

CREATE INDEX acs_rel_types_objtypeone_idx ON acs_rel_types (object_type_one);
CREATE INDEX acs_rel_types_objtypetwo_idx ON acs_rel_types (object_type_two);
CREATE INDEX acs_rel_types_role_one_idx ON acs_rel_types (role_one);
CREATE INDEX acs_rel_types_role_two_idx ON acs_rel_types (role_two);

-- Tables with foreign keys that refer to acs_rel_types:
--group_element_index(group_elem_index_rel_type_fk)
--group_rels(group_rels_rel_type_fk)
--group_type_rels(group_type_rels_rel_type_fk)
--contact_groups_allowed_rels(contact_groups_allowed_rels_type_fk)
--acs_rels(acs_rels_rel_type_fk)
--rel_segments(rel_segments_rel_type_fk)

-- Table size: 8,192 bytes
-- Table rows: 15

Tables: