jeudi 20 août 2015

How many queries do I need to get subtype specific fields in a supertype/subtype database design?

I have the following DB schema enter image description here

Each user type will have it's own specific fields based on the job they provide. Also, if the user type has a subtype, the subtype will also have specific fields.

Imagine I have the following users in my tables:
**users** table: user_id | user_type_id | username ... 1 | 1 | business_johndoe 2 | 2 | person_marykay

**persons** table: user_id | personscol 2 | "This is a person user"

**businesses** table: user_id | business_type_id | businessescol 1 | 1 | "This is a business user"

**general_staff** table: user_id | general_staff_type_id | general_staffcol 1 | 1 | "This is a business - general staff - lot attendant user"

Let's say business_johndoe logs in. I store in the SESSION his ID, which is 1. Right now, I don't know anything about johndoe except that he is logged in. I want to know what kind of user he is, if he is a business I need to know what type, and then also what subtype.

My current thinking is like this:
(for a business user)

  1. query users ID=1 for user_type_id => user_type_id=1 which means I will next query the businesses table
  2. query businesses ID=1 for business_type_id => business_type_id=1 which means that next query is on the general_staff table
  3. query general_staff ID=1 for general_staff_type_id => general_staff_type_id=1
  4. query general_staff_types for general_staff_type_id=1 => "Lot Attendant"

(for a person user)

  1. query users ID=2 for user_type_id => user_type_id=2 which means I will next query the persons table
  2. query persons ID=2 => "This is a person user"

Query #1 is similar to both options (including Administrator user I will have three options). However, after query #1 everything is different.

Do I need to have three queries and based on that all the other queries that follow in case the user is a type that has subtypes (multiple subtypes)?

Is there a way on doing this in a different way (change application logic or change database design)?

P.S: I will be using Laravel 5 so I will use extensively Eloquent Models!

laravel php laravel 5 laravel 4 laravel with laravel tutorial

Aucun commentaire:

Enregistrer un commentaire