SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Custom Fields Q

Hybrid View

  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Custom Fields Q

    I want to allow a person to insert custom fields and was wondering what's considered best practice:


    A: Create a new table "custom_user_fields" to store the fields the admin wants,
    and Create another table "custom_user_data" to store the data related to the fields.

    or

    B: Add columns onto the existing user table.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If these fields may vary in the future, or they all won't be present for each user, then option A is appropriate.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do a search for EAV (entity-attribute-value)

    not recommended unless you have no other options

    option B is an option
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm ok, well.

    I would rather do option B, since it will apply to all users. And I think it keeps the DB Layout cleaner looking.

    Maybe I should prefix them with custom_ in the user table.

    Ill readup on the EAV thing, thanks doods.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JREAM View Post
    And I think it keeps the DB Layout cleaner looking.
    indeed it does

    but that's where the simplicity ends

    getting meaningful data out of an EAV table is more painful than a root canal

    don't take my word for it, or all the anti-EAV articles you will find when you search -- try it yourself and see

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    indeed it does

    but that's where the simplicity ends

    getting meaningful data out of an EAV table is more painful than a root canal

    don't take my word for it, or all the anti-EAV articles you will find when you search -- try it yourself and see

    Haha, I can imagine that getting sticky pretty bad.
    I had read some Wikipedia and another site, it

    Im reading a few sites its pretty funny, one guy says:

    I was brought in to a project with a database in EAV model style,
    the database is already widely deployed to customers and there is no chance
    of changing the schema because "the database design is so good that they
    haven had to change the schema for 10 years" [sic]

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I would choose B but factor out the dynamic data into another table with foreign key back to the users table. You already have a users table right? That table can pretty much stay static. Than you can add another table a users_id as the primary key only. When an admin adds a column just alter this "dynamic" table. You will just need to make sure you application data layer can easily expand with this table. So rather than hard coding select columns for the dynamic data table you should use a array of all columns or something. That way when someone adds to the table the system adapts to the change without breaking or requiring additional modification. You also maintain the 1:1 relationship between users and dynamic data, which will make selecting data, filtering, sorting, etc much more efficient than an EAV. It seems like only specific people have the rights to add columns so its not a huge task to alter the dynamic table every now and than. Much better with that than an EAV which can be a nightmare to manage.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •