How does the Users Meta table work
The users_meta table is a special kind of table that behaves differently than the rest of the tables. Like all other SQL based tables, it works the same way as any other relational table, except when it related to other tables in the database. Basically the normalization of the users_meta conforms up to the 3rd normal form when analyzed as an independent table (because of it's nature). But how it relates to other tables is how the users_meta table changes it's concept idea.
users_meta is used to store any additional information added to any other table in the database. This information typically comes from the following sources but may include others:
- Custom Forms
- Custom Stored Variables
- Additional Fields added to a form
- Any additional source of input to the database that might include a variable or column that does not exist on the system by default
If the system detects a new variable it will create a "virtual" column for it inside the users_meta table and it works something like this:
Let us assume that the system offers by default a form called Member Technology and this form includes the following fields (already default in the system):
- Years of Experience
- Programming Language
- Favorite Programming Language
- Spoken Languages
- Native Spoken Language
This form is fine for most uses, but a partner would like to expand this form to include the following fields:
- Project Management (Git, SVN, HG...)
- Sites used for knowledge (StackOverflow, AskUbuntu, Udemy, Lynda...)
- Personal Website
- Nickname
The end result would be a form with 9 fields (5 by default and 4 custom ones). How would the system manage this new form. How would it know what information from the custom fields relates to what information from the system default ones.
This is where the users_meta comes in. It offers the following columns that, when they are related with another table, behave differently. This columns are:
- database
- database_id
- key
- value
This columns merge with any relating table by what values they have. So in an example when a user queries the users_data, the system also queries the users_meta table to check if there is any additional information that is not found in the users_data but pertains to the users_data table, so what happens is the following:
1. User queries the users_data table. This immediately makes the system also check the users_meta table for any additional information it may have related to the users_data table.
2. users_meta is searched for all values in the database column that are named "users_data". This is done to verify what values match with the values found in the users_data table. If there are any "users_data" values in the database column, then it proceeds to the next step.
3. Once a match is found in the database column, it checks in the database_id column for values that match existing users_data table primary keys that exist. So if there is a user in the users_data table with the user_id 7 and the users_meta table has a database_id of 7, this becomes a match.
4. Once a match is made by using the database and database_id columns, it proceeds on concatenating the values in the key column and value column to the users_meta query results the user made in the beginning. For the key and value columns, for each key column value found, the value becomes a column in the users_data queried. For each value in the value column found, each value becomes the actual value in the key column under the users_data query. To better illustrate this, we will look at the following image:
Here we can see a part of the users_meta table. If we look at the database column we can see that it holds the value "subscription_types" for multiple rows. If a user wanted to query the subscription_types column, the query result would bring all the columns from the subscription_types table and will also concatenate the following columns to the query result based on the image above:
As you can see, all values in the key column became columns and all of the values in the value column became the values for each individual key column.
This same behavior happens for any other table that is queried on the system. Every time any table is queried, it will also check in the users_meta for any relevant information that can be merged before returning the query result. So, as base knowledge you can remember that:
- database - Used to match the relation between users_meta and the table queried (eg: users_data, data_posts, etc...)
- database_id - Used to match the specific row in the users_meta with the specific row in the table queried (eg: users_data, data_posts, etc...)
- key - Used as the column name and it is merged with the final query result
- value - Used to assign the value to the merged columns that were created by the key column (mentioned above)
