title: Search with data in two different systems?
body: <p>I need to write search functionality for a website full of videos.  The videos are hosted by a 3rd party online video platform (OVP) like brightcove, kaltura, ooyala etc...  The OVP offers a search API.  The OVP holds the following information for each video: title, description, is_published, duration, tags and create_date.   The search API easily performs search and sorting on these fields.</p>
  
  <p>However, I would like to ascribe more information to each video such as: available close cap langauges, number of views, number of likes, most popular in past month etc...  I suppose the best way for me to store this data is in my own database table, with a FK video_id that relates to the video_id in the OVP's video database table.</p>
  
  <p>If I want to search for videos across fields in both the OVPs video database table via their API and the my own video database table <code>tbl_video_meta_data</code> via SQL statements, how should I do it?</p>
  
  <p>I thought of two solutions, but not sure if either of them are a good idea, or if there are other alternatives to consider</p>
  
  <p>1) Perform a search via the OVP search API based on its supported fields.  Then perform a separate search within my own <code>tbl_video_meta_data</code> based on its own available fields.  Then display records that are common to both search results.  In this approach, I'm worried about the performance of doing two separate searches, and then filtering them again at the coding level instead of using SQL to do all this.</p>
  
  <p><strong>2) I should have a cronjob that periodically fetches video data from the OVP and loads it to a <code>tbl_video_cache</code>.  <code>tbl_video_cache</code> is truncated each time this happens.  My table <code>tbl_video_meta_data</code> will of course have an FK <code>video_id</code> that relates to <code>tbl_video_cache</code>. Now I can perform a SQL search on both tables via <code>JOIN</code>.  Actually, now that I think about it....this seems to be the best approach.</strong></p>
  
  <p>I think I will go with 2, but curious to know if there are any drawbacks to it.</p>