Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

USER

User_ID (PK), BUYER_ID(FK), VENDOR_ID(FK).

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

2 Answers

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

share|improve this answer
add comment

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.

user
-----
userId (pk)

vendor
------
userId (pk, fk to user.userId)

buyer
-----
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
add comment

Your Answer

 
discard

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.