Correct database schema?

Question

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);

2. 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?

Answer 1

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 ?