I am trying to design a database that has supertype/subtypes. I have the following users:
- basic user
- business user
- administrator (super user).
All three users share a common table (users) where fields like name, email, password, etc are stored. However, the business users have a separate table (business) where the business fields like business_name, business_license, business_email, etc are stored. My problem is that my business users are themselves split into 5 or more categories.
My business users are split like this:
- vehicle detailers
- lot attendants
- service technicians:
- engine technicians
- transmission technicians
- computer technicians
- electrical technicians
- sales representatives
- physical location sales representative
- internet sales representative
- sales manager
I currently designed this database but I not really happy with it. Is there a better approach?
EDIT 1 I need to mention that the business types and subtype do not have any different fields. They business table holds all I need. The business types and subtype are more like an ENUM, but held in a table.
Example of SELECTs that I will need. More exactly, how a result should look like:
username | user_type | business_name | business_type | business_subtype John Doe | business | AUDI Dealership | service technician | engine mechanic Mary Liu | basic user | NULL | NULL | NULL George Vangh | super user | NULL | NULL | NULL Martha Ayla | business | AUDI Dealership | sales | sales representative Carlos M | business | Ford & Lincoln | sales | sales manager
Found this example online and I thought I'll give it a try. Is this acceptable?