Take the 2-minute tour ×

I've got two tables - Videos and Playlists

Typically I would create the two respective tables and a multi-value table for the 1-Many relationship, such that :

Videos = [pk, videoId, title, etc..]
Playlists = [pk, title, etc..]
Playlists_Videos = [VideosFK, PlaylistFK]

Alternatively, I've been thinking about creating the two tables only and having a field in the Playlists table that has a comma separated string of VideoFK's. My application could 'split' on the id string and then retrieve elements from a cache according to the array of id's, so:

Playlists = [pk, videoIds, title, etc..]

where example data could look like:
Video - 1, "abcd", a title,...
Video - 2, "efgh", another title,...
Video - 3, "ijkl", and another one,...
Video - 4, "mnop", and another one,...

Playlist - 1, "abcd, efgh, ijkl, mnop",...

With this structure, if I want to do a lookup for all the playlists that contain a video in it, i could setup a query using like:

 FROM Playlists 
WHERE videoIds like '%VideoID%';

and otherwise I could perform a standard join query in the traditional model:

  FROM Playlists 
 WHERE pk in (SELECT PlaylistFK 
               FROM Playlists_Videos pv 
              WHERE VideoFK = '%videoPK%');

While that works, I want to understand what I'm getting myself into and am curious what MySQL does on the lower level. What are the caveats? How does this perform when scaling?


Edit -- the "videoIds" are contained within a varchar that is a comma separated string of unique string video identifiers (the YouTube Video ID)

share|improve this question
add comment

1 Answer

Think about this for a moment:

WHERE videoIds like '%VideoID%';

If the videoid value is 1, that would match on:

  • 10
  • 11
  • 21
  • 31
  • etc...

For your own sake (and ours), use the three table setup:

  • Videos = [pk, videoId, title, etc..]
  • Playlists = [pk, title, etc..]
  • Playlists_Videos = [VideosFK, PlaylistFK]
share|improve this answer
right, instead of thinking about VideoID as an integer key, i should have been explicit in stating that it's a unique string. (its the youtube video id) –  Nirvana Tikku Aug 15 '10 at 0:47
add comment

Your Answer


