Mysql – Supertypes and subtypes

database-designMySQLsubtypesuser-defined-type

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:

  1. painters
  2. vehicle detailers
  3. lot attendants
  4. service technicians:
    1. engine technicians
    2. transmission technicians
    3. computer technicians
    4. electrical technicians
  5. sales representatives
    1. physical location sales representative
    2. internet sales representative
    3. sales manager

I currently designed this database but I not really happy with it. Is there a better approach?

enter image description here

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.

EDIT 2
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

UPDATE 3
Found this example online and I thought I'll give it a try. Is this acceptable?

enter image description here

Best Answer

Yes the code in Update 3 will work and you will be able to select the information you need. If you are comfortable with that then go for it. Keep in mind that means you will have to either duplicate your Painter type in both the sub and super type, or leave Painter as a sub type without a super type. Either way this is a little bit awkward but workable.

Assuming the tables really are as small as you say,and your requirements as simple as they seem you might want to consider compressing the business data down into one table. It won't look as sexy as other solutions but CRUD operations will be dead simple. I don't know MySql very well so please excuse any silly syntax errors:

CREATE TABLE users (user_id INT NOT NULL, user_type_id INT NOT NULL, busniess_id INT, etc);
CREATE TABLE user_type (user_type_id INT NOT NULL, name VARCHAR(50) );
CREATE TABLE business (business_id INT NOT NULL, business_name VARCHAR(50) NOT NULL, business_type_id INT NOT NULL, business_subtype_id INT, etc);
CREATE TABLE business_type (business_type_id INT NOT NULL, name VARCHAR(50) );
CREATE TABLE business_sub_type (business_sub_type_id INT NOT NULL, name VARCHAR(50) );

Like I said that isn't sexy but it will get the job done. It is a bummer that MySql doesn't offer check constraints because they could offer a touch of protection.

If your requirements get any more involved I would seriously reconsider @MDCCL's post. My gut says this is the best solution out of the mostly ho-hum solutions I can envision. To quote another answer to that question :

The Answer by MDCCL is fascinating, educational, and presumably correct (though above my pay-grade).

Related Question