Skip to main content
added 58 characters in body
Source Link
Neil McGuigan
  • 8.6k
  • 5
  • 40
  • 56
--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that canplays bethe role of a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v'); 
commit;    

--create a company that canplays bethe role of a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);

Fiddle with it here: http://sqlfiddle.com/#!15/a76fd/2

--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that can be a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v'); 
commit;    

--create a company that can be a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);
--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that plays the role of a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v'); 
commit;    

--create a company that plays the role of a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);

Fiddle with it here: http://sqlfiddle.com/#!15/a76fd/2

Source Link
Neil McGuigan
  • 8.6k
  • 5
  • 40
  • 56

--this is postgres syntax, but similar for mysql
--using single table inheritance for simplicity

--organizations (o) and individuals (i) go here:

create table parties (
  party_id int primary key,
  type char(1) not null check (type in ('i', 'o') ), --use a lookup table in real life
  full_legal_name varchar(255) not null
);

--parties that play the role of vendor (v) or buyer (b) go here. parties can play many roles:

create table party_roles (
  party_id int references parties(party_id),
  type char(1) check ( type in ('v', 'b') ), --use a lookup table in real life

  --add extra role info here

  primary key (party_id, type)
);

--sales orders go here. foreign keys ensure vendor is a vendor role, etc

create table sales_orders (
  order_id int primary key,
  order_date timestamptz not null default current_timestamp,
  vendor_party_id int not null,
  vendor_type char(1) not null default 'v',
  buyer_party_id int not null,
  buyer_type char(1) not null default 'b',

  foreign key (vendor_party_id, vendor_type) references party_roles(party_id, type),

  foreign key (buyer_party_id, buyer_type) references party_roles(party_id, type)
);

--create a company that can be a vendor:
begin;
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v'); 
commit;    

--create a company that can be a buyer:
begin;
insert into parties values (2, 'o', 'Globalcorp.biz');
insert into party_roles values (2, 'b');
commit;

--create a sales order, vendor is acme, buyer is globalcorp:
insert into sales_orders (order_id, vendor_party_id, buyer_party_id) values (1, 1, 2);