Tools Blog Learn Quizzes Smile API Log In / Sign Up
Tools Blog Learn Quizzes Smile API Log In / Sign Up
« Return to the tutorials list
We have updated our privacy policy to let you know that we use cookies to personalise content and ads. We also use cookies to analyse our traffic and we share information about your use of our site and application with our advertising and analytics partners. By using this website or our application you agree to our use of cookies. Learn more about the way this website uses cookies or remove this message.

How to structure your MySQL database tables for storing custom fields

Difficulty: 25 / 50 Tweet
chain links

In this tutorial you will learn a little bit about how to store an unknown number of parameters linked to an object in your MySQL tables.

Here's a possible scenario that will help you better understand how this works:

We're assuming that a script imports data about users in a system and the only "known" elements are the userid and username from a table called user. All the other "attributes" that a userid may have are unknown (ex: One user could have gender and hair color, while another could have gender, age, and favorite pet).

For such cases you should design your tables having in mind the 1 to n relationship A.K.A. one-to-many - one object on one side relates to n objects on the other side through a common element (in our case the userid column).

This being said, your users table would end up having an auto incremented, integer userid column and a unique, varchar username column. The other table should have an auto increment, the userid column which should be indexed for fast joins, the name of the value to store (ex: hair color) and the value column (ex: blonde). Of course, the two "unknown" columns can only be "VARCHAR" as you don't really know what the possibilities are.

  
    users:
      +--------+-----------+
      | userid |  username |
      +--------+-----------+
      |      1 |codepunker |
      +--------+-----------+
      |      2 |developer  |
      +--------+-----------+

    userdetails:
      +----+--------+-----------+---------+
      | id | userid | attr_name | attrval |
      +----+--------+-----------+---------+
      |  1 |      1 | haircolor |  blonde |
      +----+--------+-----------+---------+
      |  2 |      1 |    gender |    male |
      +----+--------+-----------+---------+
      |  3 |      2 |    gender |  female |
      +----+--------+-----------+---------+
      |  4 |      2 |       age |      10 |
      +----+--------+-----------+---------+
      |  5 |      2 |       pet |     dog |
      +----+--------+-----------+---------+
  

Next question that logically follows the above statements is: How do you query this sort of table structure ? Here are a few examples:

  
  -- get all users that have pets
      SELECT u.username FROM users AS u 
        INNER JOIN userdetails AS d ON u.userid = d.userid 
        WHERE d.attr_name = 'pet';

  -- get the gender for each user
      SELECT u.username, d.attrval FROM users AS u 
        INNER JOIN userdetails AS d ON u.userid = d.userid 
        WHERE d.attr_name = 'gender';
  

Wait! It's not over! This will get even nicer if we separate the name of the attribute into another table. Here's the table structure I am proposing:

  
    users:
      +--------+-----------+
      | userid |  username |
      +--------+-----------+
      |      1 |codepunker |
      +--------+-----------+
      |      2 |developer  |
      +--------+-----------+

    userattributes:
      +--------+-----------+
      | attrid | attribute |
      +--------+-----------+
      |      1 | haircolor |
      +--------+-----------+
      |      2 |    gender |
      +--------+-----------+
      |      3 |       age |
      +--------+-----------+
      |      4 |       pet |
      +--------+-----------+

    userdetails:
      +----+--------+--------+---------+
      | id | userid | attrid | attrval |
      +----+--------+--------+---------+
      |  1 |      1 |      1 |  blonde |
      +----+--------+--------+---------+
      |  2 |      1 |      2 |    male |
      +----+--------+--------+---------+
      |  3 |      2 |      2 |  female |
      +----+--------+--------+---------+
      |  4 |      2 |      3 |      10 |
      +----+--------+--------+---------+
      |  5 |      2 |      4 |     dog |
      +----+--------+- ------+---------+
  

Here are some query examples:

  
  -- get all users that have pets
      SELECT u.username FROM users AS u 
        INNER JOIN userdetails AS d ON u.userid = d.userid 
        INNER JOIN userattributes as a ON d.attrid = a.attrid
        WHERE a.attr_name = 'pet';

  -- get the gender for each user
      SELECT u.username, d.attrval FROM users AS u 
        INNER JOIN userdetails AS d ON u.userid = d.userid 
        INNER JOIN userattributes as a ON d.attrid = a.attrid
        WHERE a.attr_name = 'gender';
  

P.S. I haven't fully tested the above queries so if I missed something please let me know in your comments.

comments powered by Disqus