Professional Web Applications Themes

separate table vs. array-like list field - MySQL

I'm very, very new to mysql and I've been toying with it the last couple weeks. I've hit upon a theoretical question that I would like to run by more experienced users. One of the things I've been doing to teach myself is creating databases and tables that might be similar to databases powering big, popular sites like Flickr or Digg. For example, I created a user table (id [primary key], name, nickname, email, location, birthday, profile, etc.) and then played around with querying it in various ways. One of the things I did to learn how to use "join" ...

  1. #1

    Default separate table vs. array-like list field

    I'm very, very new to mysql and I've been toying with it the last
    couple weeks. I've hit upon a theoretical question that I would like to
    run by more experienced users.

    One of the things I've been doing to teach myself is creating databases
    and tables that might be similar to databases powering big, popular
    sites like Flickr or Digg. For example, I created a user table (id
    [primary key], name, nickname, email, location, birthday, profile,
    etc.) and then played around with querying it in various ways.

    One of the things I did to learn how to use "join" was to create a
    separate table for blocked users. Sites like Flickr and Digg often give
    users the ability to block other users. The way I implemented this was
    to create a table with the blocker's id and the "blockee's" id. Using
    that, I can query the blocked users table and cross-reference it to the
    user list and come up with the list of blocked users for any specified
    user. Pretty cool.

    So I wonder. On a big site with a million+ users, would having to query
    a separate table for a list of blocked users like that bog down the
    site or would it be preferable (in terms of speed and efficiency) to
    add a field to the user table that lists the id numbers of that user's
    blocked users, sort of a pseudo-array that I collapse and expand when
    needed (e.g., a blocked user array-like list might look like
    "150|11516|61878|9091|41064" etc.

    Any thoughts? Or am I still so new at this that this scenario doesn't
    make sense?

    --Rick

    Rick Guest

  2. #2

    Default Re: separate table vs. array-like list field

    "Rick" <inkswamphotmail.com> wrote in message
    news:1142819964.329383.55800t31g2000cwb.googlegro ups.com...
    > So I wonder. On a big site with a million+ users, would having to query
    > a separate table for a list of blocked users like that bog down the
    > site or would it be preferable (in terms of speed and efficiency) to
    > add a field to the user table that lists the id numbers of that user's
    > blocked users, sort of a pseudo-array that I collapse and expand when
    > needed (e.g., a blocked user array-like list might look like
    > "150|11516|61878|9091|41064" etc.
    It's better to use another table. There are several reasons for this.
    - You can create constraints so that you know each blocker/blockee id in the
    table corresponds to an existing entry in the users table.
    - You don't have to worry about the string growing too long to fit in the
    field; just add records as needed.
    - You create and use your database based on the data model, not the physical
    model (this is kind of an abstract reason and hard to appreciate if you're
    new to database programming, but it more makes sense after you've had to
    manage badly-designed databases for a while).
    - Doing the join is something an RDBMS is designed to do efficiently.
    Expanding/collapsing a long array of numbers into a string is not. There
    are internal features (indexes) that may actually make the join be more
    speedy and efficient than implementing the same model using a string like
    you describe.

    Also, adding and deleting elements by inserting/deleting records is easier
    and more efficient. Take for example deleting. You'd have to fetch a
    really long string, search for the relevant id number, make a new string
    consisting of the prefix and suffix of the string, and then update the table
    to save the new string. Compare with deleting a row simply by using DELETE
    with appropriate WHERE criteria.

    No doubt there are other reasons why it's better to use the separate table
    instead of the list-in-a-string.

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: separate table vs. array-like list field

    Rick wrote:
    > I'm very, very new to mysql and I've been toying with it the last
    > couple weeks. I've hit upon a theoretical question that I would like to
    > run by more experienced users.
    >
    > One of the things I've been doing to teach myself is creating databases
    > and tables that might be similar to databases powering big, popular
    > sites like Flickr or Digg. For example, I created a user table (id
    > [primary key], name, nickname, email, location, birthday, profile,
    > etc.) and then played around with querying it in various ways.
    >
    > One of the things I did to learn how to use "join" was to create a
    > separate table for blocked users. Sites like Flickr and Digg often give
    > users the ability to block other users. The way I implemented this was
    > to create a table with the blocker's id and the "blockee's" id. Using
    > that, I can query the blocked users table and cross-reference it to the
    > user list and come up with the list of blocked users for any specified
    > user. Pretty cool.
    >
    > So I wonder. On a big site with a million+ users, would having to query
    > a separate table for a list of blocked users like that bog down the
    > site or would it be preferable (in terms of speed and efficiency) to
    > add a field to the user table that lists the id numbers of that user's
    > blocked users, sort of a pseudo-array that I collapse and expand when
    > needed (e.g., a blocked user array-like list might look like
    > "150|11516|61878|9091|41064" etc.
    >
    > Any thoughts? Or am I still so new at this that this scenario doesn't
    > make sense?
    >
    > --Rick
    >
    First of all, you should check into database normalization. It helps
    understand how to design databases.

    Additionally, it is much more efficient to let the database do the
    searches rather than try to explode a field yourself, especially when
    you get into a lot of id's.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: separate table vs. array-like list field

    >I'm very, very new to mysql and I've been toying with it the last
    >couple weeks. I've hit upon a theoretical question that I would like to
    >run by more experienced users.
    >
    >One of the things I've been doing to teach myself is creating databases
    >and tables that might be similar to databases powering big, popular
    >sites like Flickr or Digg. For example, I created a user table (id
    >[primary key], name, nickname, email, location, birthday, profile,
    >etc.) and then played around with querying it in various ways.
    >
    >One of the things I did to learn how to use "join" was to create a
    >separate table for blocked users. Sites like Flickr and Digg often give
    >users the ability to block other users. The way I implemented this was
    >to create a table with the blocker's id and the "blockee's" id. Using
    >that, I can query the blocked users table and cross-reference it to the
    >user list and come up with the list of blocked users for any specified
    >user. Pretty cool.
    You can also do other things joining this table against other
    tables, like "get me a list of articles whose authors are NOT
    on the blocked list of this user".
    >So I wonder. On a big site with a million+ users, would having to query
    >a separate table for a list of blocked users like that bog down the
    >site or would it be preferable (in terms of speed and efficiency) to
    >add a field to the user table that lists the id numbers of that user's
    >blocked users, sort of a pseudo-array that I collapse and expand when
    >needed (e.g., a blocked user array-like list might look like
    >"150|11516|61878|9091|41064" etc.
    Consider what happens when you have a million+ users, EACH OF WHOM
    BLOCK HALF OF THE OTHER USERS. All that exploding and unexploding
    can get expensive. And you have to be prepared for the huge string
    lengths even if one user does that. Now, how do you retrieve a
    list of articles by unblocked authors? Assuming there's a reason
    to do it, how do you retrieve a list of users and how many other
    users blocked them?
    >Any thoughts? Or am I still so new at this that this scenario doesn't
    >make sense?
    Indexes can speed up lookups enormously. Your blocked user list
    with an index in (blocking user, blocked user) can quickly check
    the presence of an entry even if there's half a trillion entries
    in the table.

    Gordon L. Burditt
    Gordon Burditt Guest

  5. #5

    Default Re: separate table vs. array-like list field

    Thank you very much, all three of you.

    Strangely, I would have bet (prior to this) that the pseudo-array
    approach would have been faster, but I appreciate the three of you
    taking the time to answer so thoroughly. What you've written makes a
    lot of sense and shows me where I'm making assumptions I ought not to
    make.

    Rick Guest

  6. #6

    Default Re: separate table vs. array-like list field

    Rick wrote:
    >Thank you very much, all three of you.
    >
    >Strangely, I would have bet (prior to this) that the pseudo-array
    >approach would have been faster, but I appreciate the three of you
    >taking the time to answer so thoroughly. What you've written makes a
    >lot of sense and shows me where I'm making assumptions I ought not to
    >make.
    >
    >
    >
    And thanks for asking this question. As a novice user of databases I
    have run into situations where storing comma separated data in a column
    seemed to make sense, and I have wondered about this myself.

    --
    *****************************
    Chuck Anderson Boulder, CO
    [url]http://www.CycleTourist.com[/url]
    Integrity is obvious.
    The lack of it is common.
    *****************************
    Chuck Anderson Guest

Similar Threads

  1. Binding form field to table field.
    By Bill in forum Microsoft Access
    Replies: 2
    Last Post: January 9th, 11:41 AM
  2. List from array .as
    By Salem_ in forum Macromedia Flex General Discussion
    Replies: 4
    Last Post: February 8th, 04:46 AM
  3. Replies: 2
    Last Post: September 23rd, 07:56 AM
  4. separate list in pages x
    By Bart Plessers \(artabel\) in forum ASP
    Replies: 2
    Last Post: September 14th, 08:31 AM
  5. Replies: 3
    Last Post: July 14th, 11:50 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139