#1
  1. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    971

    Meta and Pivot tables for user information


    At work we've designed too applications that allow the administrator to decide which fields a user's profile has. Each field can be modified, deleted or created, even first and last names which seems like a bad idea to me, but it's what the client wanted.

    When we were first designing the database, well, when I was designing it I thought that using Wordpress's table structure for its posts would make sense. It keeps some information that will always be used in the wp_posts table and allows "meta" data to be added and stored in wp_postmeta, so that a developer could add options and information to a post without altering the posts table. So with that in mind, I created three tables, one was a basic user table with the following columns:

    PHP Code:
    user table
    id email status type
    1 test
    @sample.com 1 2 
    2 test
    @test.com 1 3 
    The other was the meta table to store profile data:

    PHP Code:
    user_meta table
    id label
    1 First Name
    2 Last Name 
    Where label is what shows up on the profile such as "First Name", "Last Name" etc. The third table is the user and profile data:

    PHP Code:
    user_field_value table
    id mid userid value
    1 1     1        
    'Test'
    2 2     1        'User'
    3 1     2        'Second'
    4 1     2        'User' 
    Mid being the meta table's primary key, userid obviously the id of the user being stored, value the actual data.

    All of this was okay in theory, but as we went about development, the amount of legwork we had to do to retrieve labels and values out of the tables was a little absurd. If we were just pulling first and last names, that's two simple joins to do, but if I want to pull out their name, where they live, their age the number of joins gets unwieldy and we were forced to pull all the available field labels out into a PHP array and then iterate over all the rows in the user_field_value table and match up labels to values for each user's id. I'm sure that there's a better solution to this problem than what we're doing - I did try looking up pivot tables in MySQL, but it looked like they were better suited to aggregating sums from a table. If anyone's done something like this before, or has any ideas, I'd appreciate it. What we have now works, but it's clumsy.
    "Those who can make you believe absurdities can make you commit atrocities."
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Jyncka
    All of this was okay in theory, but as we went about development, the amount of legwork we had to do to retrieve labels and values out of the tables was a little absurd.
    interesting observation

    what you've described is more generally known as an "EAV" scheme -- entity-attribute-value

    only you can decide whether the full and unfettered flexibility it offers is worth the complexity, overhead, and poor performance

    an alternative strategy is simply to keep adding new columns to the users table as the need for them arises, with all irrelevant columns for any particular user being populated with NULLs

    this puts the onus on the application layer for handling what to display, but the queries are guaranteed simple and efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    971
    I don't think the alternative strategy would be a bad idea, but what would concern me is altering the table every time a new profile field was created or an old one dropped. It's probably a novice fear, but I get worried when my application is altering my database tables outside of an installation script that would create the tables for the first time or a "dummy data" script that merely drops all data and then inserts test data.

    But I will read about EAV schemes, I know I've heard of that term, but I don't know a lot about it. I'd like to see if my current solution could be improved at all.

    Thank you r937 for the advice, this has been bothering me for awhile but I haven't been able to think of another way to do it.

    Contemplative Edit:

    I just wanted to add here, that thinking about going with an EAV design for our users where we assumed that first and last names would not be part of their profiles was a bad call. In both applications (for two different clients), we used both these fields and they are by far the most commonly retrieved values. If the main users table had these fields in it, those particular queries would be easier to read and simple. I'm the more junior of the backend developers here and I was hesitant to speak up when we were first designing how the application worked, but in the future I will definitely use what I learned to better my work.
    Last edited by Jyncka; March 29th, 2013 at 01:55 PM.
    "Those who can make you believe absurdities can make you commit atrocities."

IMN logo majestic logo threadwatch logo seochat tools logo