How to properly structure multiple user types? So i'm building a database for an app/website. It's essentially a e-commerce marketplace/platform where a brand can sign up to upload their products and customers can also then sign up to purchase any products from any various brands.
A brand and or employee cannot be a customer
A customer cannot be a brand
This is the structure I have so far, but it seems to me that from this diagram it implies that a user can be a customer, brand and brand employee at the same time.
Is there a better optimal structure for this?
You could turn around the foreign key relationships and add three foreign keys to user, one for each of customer, brand and brand_employee. Then add a CHECK constraint that makes sure that exactly one of the foreign keys is NOT NULL:
CREATE TABLE users (
   id                uuid PRIMARY KEY,
   ...
   customer_id       uuid REFERENCES customer(id),
   brand_id          uuid REFERENCES brand(id),
   brand_employee_id uuid REFERENCES brand_employee(id),
   CHECK (customer_id IS NOT NULL AND brand_id IS     NULL AND brand_employee_id IS     NULL OR
          customer_id IS     NULL AND brand_id IS NOT NULL AND brand_employee_id IS     NULL OR
          customer_id IS     NULL AND brand_id IS     NULL AND brand_employee_id IS NOT NULL)
);