I'm trying to structure a database for a e-commerce application.
They'll be two types of users: Customer and Staff (with staff being able to have different permissions)
I would like to have it so a customer cannot be a staff member and a staff member cannot be a customer.
A staff member can only be assigned to one brand.
Here's what I've got.
It's NOT complete so if you suggest to combine a table because it has similar columns, most likely I'll be adding attributes.
How do I structure this?
Add a column is_staff boolean to the user table and include it in the primary key or a UNIQUE constraint. Then include that column in the foreign key definition. Finally, have a CHECK constraint on staff and customer that makes sure that the value is correct.
As an aside, don't name a table user, because that is a reserved word in SQL. Also, use only lower case letters for table and column names, so that you don't need to use double quotes all the time.
Here is an example:
ALTER TABLE "user" ADD user_type text CHECK (user_type IN ('staff', 'customer'));
ALTER TABLE "user" ADD UNIQUE (id, user_type);
ALTER TABLE staff ADD user_type text CHECK (user_type = 'staff');
ALTER TABLE staff ADD FOREIGN KEY (id, user_type) REFERENCES "user" (id, user_type);
ALTER TABLE customer ADD user_type text CHECK (user_type = 'customer');
ALTER TABLE customer ADD FOREIGN KEY (id, user_type) REFERENCES "user" (id, user_type);
id and user_type unique, which is completely superfluous, since id as primary key is already unique. But a foreign key needs a primary key or unique constraint as destination, so it cannot be avoided.