3

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

  • 1
    @rickjames Well I am designing with the SELECTs in mind. I will have more READs than anything else. However I don't understand the "tables in a vacuum". What am I doing wrong? – Cristian Aug 10 '15 at 18:06
  • The business types and subtype are not different. I only need this table(s) to recognize what kind of business they belong to. So no, a computer tech has same fields in the business table as the engine tech. – Cristian Aug 11 '15 at 1:45
  • The reason I need to separate sales vs technicians (and others) is because the permissions each receive. However, they do not need extra fields and the common ones of the user table are just enough. My supertypes and subtypes could be considered like categories. I need a way to store them in the DB so that for example when someone signs up to be able to provide them the list of posibilities (the types and subtypes) and nased on that assign them permissions. – Cristian Aug 13 '15 at 17:38
1

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

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.