Romber

Database tables

romber_member.relationships

• Purpose
• Definition
• Constraints
• Load

Purpose

This table provides a list of valid member relationships. This information is highly dynamic and does need a data entry page.

Definition

Source romber_member_relationships_tbl.sql

create table romber_member.relationships (
  member_basemember_a_id        romber_member.basemember_id             not null,
  member_basemember_b_id        romber_member.basemember_id             not null,
  member_relationship_a_id      romber_member.relationship_id           not null,
  member_relationship_b_id      romber_member.relationship_id           not null,
  member_relationship_id        romber_member.relationship_id           not null
                                  default nextval ( 'romber_base.generic_id_seq' ),
  member_relationship_type_id   romber_member.relationship_type_id      not null );

Constraints

Source romber_member_relationships_pk.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_pk
    primary key ( member_relationship_id );

Source romber_member_relationships_fk1.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_fk1
    foreign key ( member_basemember_a_id )
    references romber_member.basemembers ( member_basemember_id );

Source romber_member_relationships_fk2.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_fk2
    foreign key ( member_basemember_b_id )
    references romber_member.basemembers ( member_basemember_id );

Source romber_member_relationships_fk3.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_fk3
    foreign key ( member_relationship_a_id )
    references romber_member.relationships ( member_relationship_id );

Source romber_member_relationships_fk4.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_fk4
    foreign key ( member_relationship_b_id )
    references romber_member.relationships ( member_relationship_id );

Source romber_member_relationships_fk5.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_fk5
    foreign key ( member_relationship_type_id )
    references romber_member.relationship_types ( member_relationship_type_id );

Source romber_member_relationships_fk6.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_fk6
    foreign key ( member_relationship_id )
    references romber_meta.objects ( meta_object_id );

Source romber_member_relationships_ck1.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_ck1
    check ( member_relationship_id = 0
      or ( ( member_basemember_a_id > 0 and member_relationship_a_id = 0
          or member_basemember_a_id = 0 and member_relationship_a_id > 0 )
       and ( member_basemember_b_id > 0 and member_relationship_b_id = 0
          or member_basemember_b_id = 0 and member_relationship_b_id > 0 ) ) );

Source romber_member_relationships_ck2.sql

alter table romber_member.relationships
  add constraint romber_member_relationships_ck2
    check ( member_relationship_id = 0 and member_relationship_type_id = 0
     or member_relationship_id > 0 and member_relationship_type_id > 0 );

Load

Source romber_member_relationships_load.sql

insert into romber_member.relationships (
    member_basemember_a_id,
    member_basemember_b_id,
    member_relationship_a_id,
    member_relationship_b_id,
    member_relationship_id,
    member_relationship_type_id )
  values (
    0,
    0,
    0,
    0,
    0,
    0 );