Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am designing a database for an application, in that I need to consider both BUYER (BUYER_ID) and VENDOR (VENDOR_ID) as a USER (USER_ID). I have designed this logic in a following design.



I am confused, whether is this good method, while considering the following things,

  1. Buyer may be a vendor.
  2. Vendor can also be a Buyer.

I have created the USERROLE Table to achieve this, but when a user is only a buyer or vendor will the above mentioned relationship table work? Will this accept Foreign key as NULL?
I need the explanation for this - "A order could be placed by a Buyer or Vendor, but a single order cannot be placed by both buyer and Vendor at the same time". Thanks in advance.

share|improve this question
Use the Party model, especially the Party Role model. – Neil McGuigan Dec 30 '13 at 18:33
up vote 1 down vote accepted
--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:
insert into parties values (1, 'o', 'Acme, Inc.');
insert into party_roles values (1, 'v'); 

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

--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:!15/a76fd/2

share|improve this answer

Assuming that the userId refers to the vendor or buyer, this design allows userId to be both a buyer and a vendor. When an order is placed, you would simply need to JOIN on the vendor and buyer tables to ensure that userId does not exist in both.

userId (pk)

userId (pk, fk to user.userId)

userId (pk, fk to user.userId)
share|improve this answer
I'm probably not fully taking into account your entire schema, however, there is nothing wrong with having flag columns that identify a user as a vendor or buyer. – Kermit Oct 31 '13 at 14:17
Thanks for your answer. I am little confused. – rathishDBA Nov 4 '13 at 10:19
You're using table inheritance here, but inheritance is the wrong choice, as it should only be used when something is and always only will be something else, like an Individual is and always only will be a Party. Should use the Role model here, as a company can be a vendor and/or a buyer over time – Neil McGuigan Dec 30 '13 at 18:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.