0

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?

enter image description here

philipxy
  • 14,867
  • 6
  • 39
  • 83
Farhan Syed
  • 77
  • 1
  • 9
  • 1
    Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 05 '20 at 06:23
  • 1
    PS Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Oct 05 '20 at 06:23
  • 1
    This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Oct 05 '20 at 06:24

1 Answers1

1

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);
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • So would you say I should just put customer and staff table into the user table? – Farhan Syed Oct 05 '20 at 06:14
  • That is another option. But I meant something else. I have added sample code to clarify what I mean. – Laurenz Albe Oct 05 '20 at 06:23
  • I see what you did there, the only thing I don't understand is why you have user_type as a unique. Isn't that making "staff" or "customer" unique? – Farhan Syed Oct 05 '20 at 09:17
  • No, it makes *the combination of* `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. – Laurenz Albe Oct 05 '20 at 15:15