SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist spinmaster's Avatar
    Join Date
    Mar 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you handle multiple user groups in a database?

    Hi there,

    I'm currently busy designing an eCommerce site to sell photography. This involves basically two different user-groups: normal users who are buying photos and photographers who upload images to sell their pictures online.

    I'm not 100% sure what the best way is to handle both user groups
    in the database, since a lot of attributes are obviously similar (name, email,
    address, etc.). However, for the photographers, certain additional attributes are obviously needed (bank details, images which have been sold, etc.)

    Furthermore, normal users should be able "to upgrade" their account to a photographer account.

    I don't think one huge table with lots of flags ("user type") is the
    best solution...someone who can give me some input pls??

  2. #2
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Location
    Wolsztyn, Poland
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One table with common basic attributes (name, email..) and the other one with attributes specific to photographers. It will be linked with the basic table with FK, so upgrading normal account will effect in adding row to the second table with proper FK.
    You can even avoid adding a user type flag in the first table if you'll get user data from both tables at once (using LEFT JOIN on the second one), because you'll get NULLs in the fields from second table, so you can tell if it is a normal user or a photographer according to that.

  3. #3
    SitePoint Evangelist spinmaster's Avatar
    Join Date
    Mar 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thx for the quick reply! anyone else?

  4. #4
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    table groups:
    group id, group name

    table profile:
    profile_id, fk_groupid, fk_profile_extra_id, Rest of common profile fields here

    table profile_extra:
    profile_extra_id, photographer extra info here


    this will leave you 1 field with a null or an id in it to grab the extra stuff.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    brian, your structure is nice, but i would eliminate the fk_profile_extra_id, and i would change table profile_extra.profile_extra_id to be table profile_extra.profile_id and make it FK to table profile.profile_id

    in fact, i would probably just combine profile and profile_extra tables

    and likely eliminate the groups table too, until i had some special columns for it

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

  6. #6
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, i guess it could go either way.

    If it were my setup, i'd like to work with a group table and a users table with a groupid foreign key.

    Null's never hurt... and it makes the "upgrade" process real easy too.


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
  •