I am currently working on a system which supports "custom fields" for users.
I will illustrate this with a simplified example of this database schema:
users
--------
user_id
name
age
custom_fields
----------------
custom_field_id
custom_field_name
user_has_custom_field_value
------------------------
user_id (FK to users table)
custom_field_id (FK to custom_fields table)
custom_field_value (column that contains value of custom field)
This schema supports creation of custom fields to store information as per what the customer wishes.
However, I want to list all users along with their custom fields in the query output (or by using some PHP logic)
| user_id | name | age | fav_sport | payment_mode | bank
----------------------------------------------------------------------------------
3 John 28 Football SWIFT Chase
6 Jane 24 Swimming Cash BOA
fav_sport, payment_mode and bank are custom fields created which are represented as 3 rows in the custom_fields table.
So, the custom_fields table looks like this :
custom_field_id | custom_field_name
-----------------------------------------------
1 fav_sport
2 payment_mode
3 bank
and the user_has_custom_field value looks like this :
user_id | custom_field_id | custom_field_value
----------------------------------------------------------------------
1 1 Football
1 2 SWIFT
1 3 Chase
2 1 Swimming
2 2 Cash
2 3 BOA
What is the best way to achieve this with minimum queries and without taxing the database ?
The only solution I can come up with is by using a query inside a for loop in PHP to fetch the custom fields (which will definitely be heavy). What are more efficient solutions to do this ?
Suggestions welcome for MySQL based, PHP based, Stored Procedures etc.
Best Answer
You have two options: either you loop results from PHP, or you store all the custom fields and values in one single JSON datatype field (stored as a json object, requires MySQL 5.7) in the users table.