Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Question "Member Profile" table structure

    Morning.

    We're developing a site where each member has a profile that can be searched upon. The contents (i.e. fields) within this profile are not fixed and can vary from person to person (e.g. one person may have "field a" as an option whereas another has "field b").

    I thought about doing this with a basic "link" style table:

    Member:
    Member_id
    Member_name

    Member_profile:
    Member_id
    Profile_type
    Profile_value

    Profile_type:
    Profile_type_id
    Profile_type_desc

    (this is a very simplified version -- the "real" version has fields that are text boxes, checkboxes, select boxes etc, but this is an example). Doing a search would then involve one inner joins to the member_profile table for every field that was searched upon.

    I've done something like this before and it's flexible enough for what I wanted, but doing a brief test -- searching through 130,000 member records with only one "member_profile" fields takes about 0.8 seconds on average (for the query); doing the same search on 7 fields (two using LIKE and one using IN) takes between 3-10 seconds for each query depending on the platform and server - I've tried MySQL and PostgreSQL.

    Another option is to simply have one central (profile) table with a column for each possible field that the user could fill out -- searching would be fast (assuming indexes were sensible!) but we could end up with one very large, unmanagable table: what would happen when (not if) we wanted to add a new possible field?

    Basically, I want to end up with a stucture that allows us to add new profile fields without too much effort but that returns SELECTs lightning quick (speed is of upmost importance!). We will be hosting on Linux and have the choice of either MySQL or PostgreSQL depending on which suits the bill best!

    Any suggestions appreciated.

    Tim.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: "Member Profile" table structure

    Originally posted by Gadget
    Morning.

    We're developing a site where each member has a profile that can be searched upon. The contents (i.e. fields) within this profile are not fixed and can vary from person to person (e.g. one person may have "field a" as an option whereas another has "field b").

    I thought about doing this with a basic "link" style table:

    Member:
    Member_id
    Member_name

    Member_profile:
    Member_id
    Profile_type
    Profile_value

    Profile_type:
    Profile_type_id
    Profile_type_desc

    (this is a very simplified version -- the "real" version has fields that are text boxes, checkboxes, select boxes etc, but this is an example). Doing a search would then involve one inner joins to the member_profile table for every field that was searched upon.

    I've done something like this before and it's flexible enough for what I wanted, but doing a brief test -- searching through 130,000 member records with only one "member_profile" fields takes about 0.8 seconds on average (for the query); doing the same search on 7 fields (two using LIKE and one using IN) takes between 3-10 seconds for each query depending on the platform and server - I've tried MySQL and PostgreSQL.

    Another option is to simply have one central (profile) table with a column for each possible field that the user could fill out -- searching would be fast (assuming indexes were sensible!) but we could end up with one very large, unmanagable table: what would happen when (not if) we wanted to add a new possible field?

    Basically, I want to end up with a stucture that allows us to add new profile fields without too much effort but that returns SELECTs lightning quick (speed is of upmost importance!). We will be hosting on Linux and have the choice of either MySQL or PostgreSQL depending on which suits the bill best!

    Any suggestions appreciated.

    Tim.
    I'd go for the one table, many columns approach myself. Your super-flexible design is commonly used for the same reasons you give, but as you can see yourself, it is a nightmare to query. Unless you are really talking about THOUSANDS of columns, I don't see any issues (unless MySQL or PostgreSQL have limitations there).

    As for "what would happen when (not if) we wanted to add a new possible field?" - don't these DBMSs have ALTER TABLE ... ADD COLUMN or equivalent functionality? That's just as easy as adding another row to the Profile_Type table isn't it? And in addition you get to specify a proper datatype, check constraints etc. - rather than use a VARCHAR(100) or similar for every value.

Posting Permissions

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