0

A system for a bike repair shop has 2 types of users: Mechanics and their Customers. The schema has this structure:

  1. Table Users:
  • The boolean field is_staff defines the type of User (if is_staff is True, then Mechanic else Customer);
  1. Table Orders:
  • Field owner_id references a User whose is_staff is False;
  • Field mechanic_id references a User whose is_staff is True.

Users with is_staff == True cannot be customers.

Is this layout correct or are there better solutions?

1
  • Is your statement that "is_staff == True cannot be customers" a Business Requirement or an observation of your schema design? Oct 22 at 19:25
0

I strongly discourage that approach for several reasons:

  • it will make queries more complex to write (and for others to understand)
  • it makes referential integrity difficult to implement: declarative RI using foreign keys is not possible. You will need an approach based on custom-written triggers
  • but the most important: an employee is not the same as a customer. An employee has a salary, hire-date, job code … A customer has a credit card nr, an account balance, … While both do have common attributes (name, address, phone nr, …) it is fine for those to exist in both tables.

I would also expect that a repair shop has more tables than that, specifically on spare parts. Also you need to think about order history: do you keep previous completed orders in the same table ? Or will you keep a separate table for that ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.