Table Relationship cardinality

Question

I am designing a database for my application, I got issue in implementing the following logic.

A user may be a Vendor and also a Buyer. I have created a table for USER and created an another table called USERTYPE in that, the type had 'VENDOR' and 'BUYER' but I have to know if a user is both 'vendor' and 'buyer' by using a same email_id (User_id). Any idea or suggestion to implement this logic. Thanks in advance.

Answer 1

Yes, if a user can have many (e.g. up to 2) types, and each type of course has many users, then this is a many-to-many relationship.

Users >--------< UserTypes

Any logical many-to-many relationship needs a third physical table that references both.

Users ----< UserHasType >---- UserTypes

For example, this table could look like the following:

CREATE TABLE UserHasType (
  user_id INT,
  type_id INT,
  PRIMARY KEY (user_id, type_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (type_id) REFERENCES UserTypes(type_id)
);