--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