What table structure would you suggest for this?

Discussion in 'Databases' started by JEET, Jan 28, 2015.

0
  1. #1
    Hello,
    I've been asked to code a feature of "whats happenning" on an existing site of over 100,000 members and growing.
    People will be able to write a line ( status ) and post it. Then their friends etc will be able to see this status update on their pages. Something like twitter.

    Right now I'm thinking that with 100,000 members, this database will grow huge and selecting data can become slow.
    What kind of table structure can I use so it doesn't get too slow? (mysql)

    I'm thinking something like this for now:

    ID int, userName varchar, status varchar, postTime timestamp , INDEX( userName )

    Now if user1 opens their page, and has user2, user3 as friends, then status of both user2, user3 will be shown.

    select status from table where userName='user2' and userName='user3' ;

    Instead of using AND, I can use IN to give the query a list of users to match with.

    However, I think that in time the structure of table might cause problems in terms of speed.

    What are your thoughts? And please suggest a better structure if possible.

    Thanks
     
    JEET, Jan 28, 2015 IP
  2. Equaite

    Equaite Active Member

    Messages:
    128
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    75
    Digital Goods:
    1
    #2
    I would recommend using IDs for querying of statuses.

    SELECT * FROM statuses WHERE user_id IN (SELECT user1_id FROM friends WHERE user2_id = {me})
    Code (markup):
    Not sure how your existing tables are setup, but this is called "taxonomy".
     
    Equaite, Jan 29, 2015 IP
  3. mohanprakash

    mohanprakash Greenhorn

    Messages:
    47
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #3
    You can do this in this way

    if there is a user table already then like structure (user_id as integer,username as varchar,address as varchar etc...).
    Then,
    make another table for friends as friendstable structure like(frnd_id as int,frnd_name as varchar,user_id as integer... etc other details of friends).
    also make another table for status as statustable structure like (status_id as int,post_status as varchar,user_id as integer...etc ther details of status post)

    now write query in this way:
    select * from statustable join usertable on usertable.userid=statustable.user_id where statustable.user_id in (select frnd_id from friendstable) and usertable.userid='$currentuserid'


    where currentuserid is the id of user who is login right now
     
    mohanprakash, Feb 24, 2015 IP