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,
- Buyer may be a vendor.
- 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.
Best Answer
Fiddle with it here: http://sqlfiddle.com/#!15/a76fd/2