Understanding Member Category Relationships in MySQL
Link: https://support.brilliantdirectories.com/support/solutions/articles/12000035993
Brilliant Directories stores member categories and subcategories across multiple database tables. Understanding how these tables relate to one another is helpful when creating SQL queries, importing data, or troubleshooting category assignments.
Main Categories
The list_professions table stores all main member categories.
Each member's assigned main category is stored in the profession_id column of the users_data table.
| Table | Column | Purpose |
|---|---|---|
list_professions | profession_id | Stores the main category records. |
users_data | profession_id | Stores the member's assigned main category. |
Subcategories
The list_services table stores all subcategories and sub-subcategories.
The following columns define the relationship between categories:
| Column | Purpose |
service_id | Unique ID of the subcategory. |
profession_id | Identifies the parent main category. |
master_id | Identifies the parent subcategory. A value of 0 indicates a first-level subcategory. |
Example Category Structure
The following example shows the list_services table in phpMyAdmin.
Each record shares the same profession_id (1), indicating that all three subcategories belong to the same main category.
The master_id value is 0 for every record, indicating that these are first-level subcategories and are not children of another subcategory.
If a subcategory were nested beneath another subcategory, the master_id value would contain the parent subcategory's service_id instead of 0.
Member Relationships
The rel_services table associates members with their assigned subcategories.
The two primary columns are:
| Column | Purpose |
user_id | Member ID from the users_data table. |
service_id | Assigned subcategory from the list_services table. |
Whenever a member is assigned a subcategory, a record is created in the rel_services table linking the member to the corresponding service_id.
Summary
The category relationship is managed across four primary tables:
| Table | Purpose |
users_data | Stores member records and the assigned main category. |
list_professions | Stores all main categories. |
list_services | Stores subcategories and sub-subcategories. |
rel_services | Links members to their assigned subcategories. |
Understanding these relationships makes it easier to create SQL queries, troubleshoot category assignments, and manage custom integrations with the Brilliant Directories database.
