Dynamically retrieve fields across multiple tables
===

So I have a design where people are put into groups and those groups determine what properties are saved with each person.

So if later I decide I want to have VIP customers and record their VIP numbers I can just create a new group called "VIP Members" with a property "VIP_Number" and place those customers in that group. 

Therefore Employees and other Customers do not have that field which would be irrelevant to them.

**Now what is the best way to fetch all the required data from the separate tables for a given person? I need a solution that supports the data tables being added to at a later date.**

Here is the table design.

Notes:

 - The 'd' prefix is for a data table
 - The 'x' prefix is for a cross reference table
 - The 'p' prefix is for a property
 - I have used different length IDs to help with readability
 - John Smith is an Employee and therefore a Person also.
 - Anna Backhouse is a Customer and therefore a Person also.
 - Mr Both is both an Employee and a Customer and therefore a Person also.


**dPeople**

    ID                   pFirst_Name          pLast_Name
    ----------------------------------------------------------
    001                  John                 Smith
    002                  Anna                 Backhouse
    003                  Mr                   Both
    
**dEmployees**
    
    ID                   pBadge_Code
    ----------------------------------------------------------
    01                   MB2012
    02                   JS2012
    
**dCustomers**
    
    ID                   pPhone_Number
    ----------------------------------------------------------
    01                   1800 backhouse
    02                   1800 both
    
**dGROUPS**
    
    ID                   pGroup_Name
    ----------------------------------------------------------
    1                    People
    2                    Employees
    3                    Customers
    
**xGROUPS**
    
    dGROUPS_ID_parent    dGROUPS_ID_child
    ----------------------------------------------------------
    1                    1
    1                    2
    1                    3
    
**xPeople**

    dPeople_ID           dGROUPS_ID
    ----------------------------------------------------------
    001                  2
    002                  3
    003                  2
    003                  3

**xEmployees**

    dPeople_ID           dEmployees_ID
    ----------------------------------------------------------
    001                  02
    003                  01

**xCustomers**
    
    dPeople_ID           dCustomers_ID
    ----------------------------------------------------------
    002                  01
    003                  02


Desired result if looking up Mr Both:

    ID    pFirst_Name  pLast_Name  pBadge_Code  pPhone_Number
    ----------------------------------------------------------
    003   Mr           Both        MB2012       1800 both

Desired result if looking up John Smith:

    ID    pFirst_Name  pLast_Name  pBadge_Code  
    ----------------------------------------------------------
    001   John         Smith       JS2012     

Desired result if looking up Anna Backhouse:

    ID    pFirst_Name  pLast_Name  pPhone_Number
    ----------------------------------------------------------
    002   Anna         Backhouse   1800 backhouse


I can get a list of all the groups they are in using:

    SELECT `dGROUPS`.`ID` AS `Group ID`, `dGROUPS`.`pGroup_Name` AS `Group Name` FROM `dGROUPS`, `xGROUPS` WHERE `xGROUPS`.`dGROUPS_ID_parent` = `dGROUPS`.`ID` AND (`xGROUPS`.`dGROUPS_ID_child` IN (SELECT `dGROUPS_ID` FROM `xPeople` WHERE dPeople_ID = 003))

This is helpful when I have further sub groups such as People > Employees > Managers

I am thinking I would need to run this query and use it to form another query dynamically to get the desired results. I do not mind using two queries however one would be preferred.

Perhaps there is a solution using views?

I am using PHP and MySQL.
