1

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.

1

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.

|improve this answer|||||
  • 1
    With 5.7, there is a JSON datatype, with a lot of helper functions. – Rick James Apr 24 '16 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.