How the users_meta Table Works

Link: https://support.brilliantdirectories.com/support/solutions/articles/5000686561

The users_meta table stores custom data associated with other database tables. It allows Brilliant Directories to save custom form fields, stored variables, and other dynamic values without modifying the original database schema.

Common sources of data stored in the users_meta table include:

• Custom Forms

• Custom Stored Variables

• Additional fields added to existing forms

• Other custom data that does not exist as a default column in the database

When queried through the Brilliant Directories framework, data stored in the users_meta table is merged with the related database records, allowing custom fields to appear alongside the standard table columns.

Example

Assume the system includes a default form named Member Technology with the following fields:

• Years of Experience

• Programming Language

• Favorite Programming Language

• Spoken Languages

• Native Spoken Language

If additional custom fields are added to this form, such as:

• Project Management (Git, SVN, HG...)

• Sites Used for Knowledge (Stack Overflow, Ask Ubuntu, Udemy, Lynda...)

• Personal Website

• Nickname

The default table structure does not need to change. Instead, the custom values are stored in the users_meta table and associated with the appropriate record.

How the Relationship Works

The users_meta table uses four primary columns to associate custom data with other database tables:

Query Process

When a table is queried, the Brilliant Directories framework automatically checks the users_meta table for matching records.

The process works as follows:

1. The requested table is queried (for example, users_data).

2. The system searches the users_meta table for records where the database column matches the queried table.

3. The database_id column is used to match the corresponding record in the original table.

4. Matching key and value pairs are merged into the query results, allowing custom fields to appear as part of the returned dataset.

The following example illustrates how records are stored within the users_meta table:

(Existing image)

When the subscription_types table is queried, the framework retrieves the standard table columns and then merges any matching custom fields stored in the users_meta table.

The following image demonstrates the resulting query output:

(Existing image)

Each value stored in the key column becomes the name of a custom field, while each corresponding value stored in the value column becomes the data returned for that field.

Summary

Whenever a database table is queried through the Brilliant Directories framework, the system automatically checks the users_meta table for related custom data before returning the results.

The relationship between the tables is based on the following columns:

• database — Identifies the related table.

• database_id — Identifies the related record.

• key — Defines the custom field name.

• value — Stores the custom field value.