Romber

Database tables

romber_base.business_regions

• Purpose
• Definition
• Constraints
• Examples

Purpose

This table provides a list of which regions an business is active in. It is envisaged that this information is static and doesn't need a data entry page, however there is currently no associated load script.

Definition

Source romber_business_regions_tbl.sql

create table romber_base.business_regions (
  business_id           romber_base.business_id         not null,
  business_region_id    romber_member.relationship_id   not null,
  override_currency_id  romber_base.currency_id         not null,
  region_id             romber_location.location_id     not null );

Constraints

Source romber_business_regions_pk.sql

alter table romber_base.business_regions
  add constraint romber_business_regions_pk
    primary key ( business_region_id );

Source romber_business_regions_uk1.sql

alter table romber_base.business_regions
  add constraint romber_business_regions_uk1
    unique (
      business_id,
      region_id );

Source romber_business_regions_fk1.sql

alter table romber_base.business_regions
  add constraint romber_business_regions_fk1
    foreign key ( business_id )
    references romber_base.businesses ( business_id );

Source romber_business_regions_fk2.sql

alter table romber_base.business_regions
  add constraint romber_business_regions_fk2
    foreign key ( region_id )
    references romber_location.regions ( region_id );

Source romber_business_regions_fk3.sql

alter table romber_base.business_regions
  add constraint romber_business_regions_fk3
    foreign key ( override_currency_id )
    references romber_base.currencies ( currency_id );

Source romber_business_regions_fk4.sql

alter table romber_base.business_regions
  add constraint romber_business_regions_fk4
    foreign key ( business_region_id )
    references romber_member.relationships ( member_relationship_id );

Examples

insert into romber_base.business_regions (
    business_id,
    business_region_id,
    override_currency_id,
    region_id )
  values (
    ( select business_id
	from romber_base.businesses
	where business_code = 'ROMBER' ),
    1,
    0,
    ( select region_id
	from romber_location.region_for_business
	where region_code = 'AU' ) );