SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database -- Support Multi-value

    Question that I know the answer to (I think ).

    Let's assume you're keeping track of users and their contact information in a table e.g. USERS (userID, userName, etc.). Suppose you want to provide support to save their email (business, home, personal) or multiple IM accounts, or multiple Phone numbers. Granted it would be "bad design" to name the columns this in the user table (emailBusiness, emailPersonal, yahooIM, aimIM, ...), so for each of these that you want multi-values you essentially add them to their own table and create a typeof attribute "lookup table" for them to refer to as well as the user id that they belong to. Then you could add fields in the USERS table for primaryEmail, primaryPhone, etc, the other forms of contact in their own table would be secondary. This should cut down on NULL values, and join that must now be implemented shouldn't be that big of a deal granted the userid is indexed in the secondary tables.

    However granted you now have to perform 3 additional queries to search for all of the multi-value attributes and see if there is any data for them now vs. denormalizing the data and putting it in the USERS table.

    Your thoughts? Best decision? Any ways that this could hinder performance on a large user base? Thanks.

  2. #2
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please keep scaling in mind as well.
    --
    Regards
    Josh

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Your thoughts?
    that's a good design

    Best decision?
    works for me real well, i dunno if it's "best"

    Any ways that this could hinder performance on a large user base?
    not that i can see

    by the way, i would put the related numbers into the same lookup table, with a "typeof" attribute (email, fax, cell, im, icq, telex, whatever)

    you would only need one JOIN (not three), and only if displaying the additional numbers -- the rest of the time you would use only the main users table

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

  4. #4
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not exactly sure what you mean


    by the way, i would put the related numbers into the same lookup table, with a "typeof" attribute (email, fax, cell, im, icq, telex, whatever)
    you would only need one JOIN (not three), and only if displaying the additional numbers -- the rest of the time you would use only the main users table


    not sure what I understand here... should the following be the case?

    im_types
    imTypeID*
    imTypeName

    phone_types
    phoneTypeID*
    phoneTypeName

    address_types
    addressTypeID
    addressTypeName

    phones
    phoneID
    userID
    phoneTypeID
    phoneArea
    phoneExchange
    phoneExtension
    phoneExtra

    addresses
    addressID
    userID
    addressTypeID
    address1
    address2
    city
    state
    postalCode

    ...you get the rest ....

    or were you suggesting to create the types of table and have it container mixed fields such as
    types
    (sample values)
    YAHOO
    AIM
    GTALK
    MSN
    Business
    Home
    Mobile
    Pager
    ...
    --
    Regards
    Josh

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, i meant like this --

    create table usernumbers
    ( userid integer not null
    , typeof varchar(9) not null
    , usernumber varchar(37) not null
    , primary key (userid, usernumber)
    , foreign key (userid) references users (id)
    );
    insert into usernumbers values
    ( 23, 'cell', '(937) 555-1212' )
    ,( 23, 'email', 'fred@example.com' )
    ,( 42, 'home', '(213) 555-1234' )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This approach would require you to enforce the logic at the application level however though, so that your users are not supplying different typeof attributes (aka Yahoo Messenger vs. YAHOO or 'Cell' vs. Mobile).

    Would you also condsider adding a lookup table that can define the propertyType such as 'Phone', 'Email', or 'IM'?

    With this alternative it is impossible to query just IMs or Phone's, etc., right?
    --
    Regards
    Josh

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    This approach would require you to enforce the logic at the application level however though
    not if you wanted to control the typeofs

    Would you also condsider adding a lookup table that can define the propertyType such as 'Phone', 'Email', or 'IM'?
    yes, i would, although this requires adding application logic to add a new typeof

    With this alternative it is impossible to query just IMs or Phone's, etc., right?
    no, it isn't impossible, in fact it's dead simple
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don;t think this approach would really work as much... how would it address the instance of the Addresses (we want to support multi-value for that as well, we need to provide Street1, Street2, City, State, PostalCode)


    Additional post later....be back in a few.
    --
    Regards
    Josh

  9. #9
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We're actually caching our lookup tables into the application as a property of the Data provider once an initial lookup is done.

    Thus, I'm thinking that the only joins that will take place will be the ones between the values table and the users. Getting the Typeof can be done quicker then. Granted we will have to query the users, then query their phone #, IMs and Addresses. (4 queries vs. the one).... but I still think its better than what is being done now.
    --
    Regards
    Josh

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    "multi-values" for addresses is a lot, lot, lot different from what we were talking about

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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jtucholski View Post
    Granted we will have to query the users, then query their phone #, IMs and Addresses. (4 queries vs. the one).
    join users to addresses, and join users to numbers -- 2 queries
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you? You don't know how many records will exist in the addresses or phone numbers table for the users. Some may opt out of the ability to put them in at all, some may provide half values for half of the available types, some may provide all of the values for the available types.
    --
    Regards
    Josh

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yup, it's still two queries

    users left outer join addresses, and users left outer join numbers
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast jtucholski's Avatar
    Join Date
    Nov 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i misinterpreted what you said....

    I was counting getting the users basic info (1 query)
    Doing a Join to get the information for Phone #'s (1 query)
    Doing a Join to get the information for Addresses (1 query)
    Doing a Join to get the information for IM names (1 query)

    4 queries one after the other....

    You can't consolidate any of them into one query (.e.g pivottable is that I think that is) because you don't know how many records will be returned within each of the join queries.
    --
    Regards
    Josh

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i would still do it in only 2 queries -- one for phones and ims, which would be in the same table (single values, with a typeof code), whereas addresses are complex structures and deserve separate treatment, but are easily combined with the users's basic info in a second query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •