Mysql Php combining 3 tables

Question

basically in this web app i have an event module where user can create, edit and delete an event also can upload photo for that event. Other users either can involve in particular event by selecting I'm in button or just mentioning the event Sound's Cool. For now there is 3 tables event_photo (user upload pic will be saved here), event_in and event_cool, now i need an event activity feed, for that i need to combine all 3 tables by grouping them as photo, in and cool. So i need suggestion whether to create a single table to list these activity or should i combine all the results in php. Previously i combine all 2 results in single table but now i want to try something different. There are the table structure.

event_photo table

id      photo_url      business_id     event_id         user_id    caption    added_date
1         1111             12             2                3       test1      20130209t1
2         1122             13             3                4       test2      20130209t4
3         1133             14             2                3       test3      20130209t2

event_in table

id  event_id    user_id    date_created
1         2                3       20130209t3

event_cool

id  event_id    user_id  date_created
1         2               4        20130209t5
2         3               3        20130209t6

Now the feed will be like this, based on date_created desc t6 -> t1

User_3 says cool for  Event_3
User_4 says cool for  Event_2
User_4 added photo for Event_3
User_3 added photo for Event_2
User_3 attending  Event_2
User_3 added photo for Event_2
User_3 added photo for Event_2

Now how the table should be design, i hope this is important questions as this can resolve many problems regarding activity feed.Thanks

Answer 1

An option would be to have your three tables and query them with something similar to:

  SELECT *
    FROM
         ( SELECT user_id, 
                  event_id, 
                  date_added as date_created,
                  'photo' as type
             FROM event_photo
            UNION
           SELECT user_id, 
                  event_id, 
                  date_created,
                  'event_in' as type
             FROM event_in
            UNION
           SELECT user_id, 
                  event_id, 
                  date_created,
                  'event_cool' as type
             FROM event_cool
         ) s
  ORDER BY date_created

To simplify your code, you could create a view:

CREATE OR REPLACE VIEW v_ordered_actions AS
  SELECT *
    FROM
         ( SELECT user_id, 
                  event_id, 
                  date_added as date_created,
                  'photo' as type
             FROM event_photo
            UNION
           SELECT user_id, 
                  event_id, 
                  date_created,
                  'event_in' as type
             FROM event_in
            UNION
           SELECT user_id, 
                  event_id, 
                  date_created,
                  'event_cool' as type
             FROM event_cool
         ) s
  ORDER BY date_created;

Answer 2

You can use UNION of course: http://dev.mysql.com/doc/refman/5.1/en/union.html However, this is quite slow. Maybe better to create next table, like activity log, where to store just:

• entity_type (in/cool/photo)
• entity_id (id of row in target table)
• event_id
• user_id
• date

And select via this. When you have selected that, collect all IDs seperatelly for each target table, and get necessary info from that tables (if necessary) returned as indexed array. Foreach activity feed and assign correct data you need for it.

Hope this can help you.