#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    112
    Rep Power
    11

    MySQL table structure - single table or multiple table


    Hi ,

    I m building an app which will fetch stats
    from social media pages and stored into a database
    and provide stats within a date range and basic stats like
    most liked pages , engagement rate etc
    The app will use youtube,twitter, Facebook and Google plus etc
    Each of them have different field. May be 20% common field. .

    My question is should put all fields in one table
    or keep separate table for each media type (YouTube, Twitter etc)
    If I put in one table then there will be lots of empty field.
    If I put in a separate table then I will need to use join queries
    to produce report and it could be slow when u are dealing thousands of query

    Option 1:
    media
    Pages(media fk )
    Stats(pages fk)

    Option 2:
    Media
    Pages(media fk)
    YouTube (pageid fk)
    Twitter (pageid fk)
    Facebook (pageid fk)
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    You more or less have the answer in you own writing:
    Originally Posted by phprock
    media type (YouTube, Twitter etc)
    I would have one field "media type" in a table with Youtube, Twitter etc. as values for that field.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    112
    Rep Power
    11
    Thanks for the reply .
    As I mentioned each media type have different field type.
    Will it be any performance issue.?
    Let's say you add Facebook stats then there will empty field for
    Other media like YouTube , Twitter . If you add youtube stats
    then Facebook field and twitter field will be empty.



    Originally Posted by MrFujin
    You more or less have the answer in you own writing:


    I would have one field "media type" in a table with Youtube, Twitter etc. as values for that field.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    112
    Rep Power
    11
    See the attachment.

    Option 1 ( Keeping all stats in one single table, problem lots of empty field.Example: you enter FB data in stats then other fields(ie yt_total_subs) in Stats table will be empty)
    ============================================
    social_media
    social_pages
    stats

    Option 2( Probably: when I will compare or do report i will have to use JOIN queries)
    =====================
    social_media
    social_pages
    youtube_stats
    googleplus_stats
    instagram_stats
    twitter_stats


    Please note, there are no edit features in the App. Only Insert stats then generate report.
    I just want to know which option will be good in terms of database performance.
    Attached Images
  8. #5
  9. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    Looks like the table at bottom right corner should be called facebook_stats.

    What is the purpose of stats table in the top right corner? looks like can be removed as it has the same fields as in the stats tables for youtube and facebook.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    112
    Rep Power
    11
    Thanks.

    Ignore the bottom right table.

    There are two options. The table that will be used
    are in options . Sorry both options I put in the same diagram
    just to show concepts . Just read the table mentioned in the options
    and think which option will be good






    Originally Posted by MrFujin
    Looks like the table at bottom right corner should be called facebook_stats.

    What is the purpose of stats table in the top right corner? looks like can be removed as it has the same fields as in the stats tables for youtube and facebook.
  12. #7
  13. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    Based on the diagram you posted, the only issue I see is that you have to create a new table and modify all SQL queries every time a new social media is created.

    Unfortunately, I am not experienced enough to see how it will be possible to get all the stats turned into a more "general" stats table.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    112
    Rep Power
    11
    Thank you.

    I'm going with Multiple tables.

    Originally Posted by MrFujin
    Based on the diagram you posted, the only issue I see is that you have to create a new table and modify all SQL queries every time a new social media is created.

    Unfortunately, I am not experienced enough to see how it will be possible to get all the stats turned into a more "general" stats table.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    Juat an idea...
    what if you set up table for avaliable media, just
    mediaID
    mediaCode
    MediaFullName
    and table Media_Stats like
    mediaID
    elementCode
    ElementValue
    asOfDate

    second pable you can prtition by elementCode and/or maybe asOfDate
    and make primary index mediaID + elementCode + asOfDate

    it will be universal and serve your needs

    Comments on this post

    • MrFujin agrees : might be possible, but see my comments below
  18. #10
  19. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    Was thinking something about that, but you will still need to define some standard naming if you want to make counts of view, downloads etc.

    Don't know if it is good or bad to let the application code (PHP) control that part.

IMN logo majestic logo threadwatch logo seochat tools logo