StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan you do this with a database?
text
Body
copied!<p>I store all the videos and the details about each one in a database. I have some tables:</p> <ul> <li>video (details about the video, name etc.)</li> <li>video_file (each video is rendered with different bit rates and for different screen sizes)</li> <li>playlist (to store videos in predefined playlists)</li> </ul> <p>Each video has files encoded for 1080 and 720, and each of those are again encoded with three different bit rates, and each of these bit rates are again encoded with both WebM and MP4. So there are six entries in the video_file per video entry. So my query returns 12 rows per video.</p> <p>The problem I see is that for each row, ALL the information is included, not only the "neccecary" information. Example:</p> <pre><code>| id | name | playlist | file | quality | size | type | --------------------------------------------------------------------- 1 video1 funny videos file1 3 720 MP4 1 video1 funny videos file2 2 720 MP4 1 video1 funny videos file3 1 720 MP4 1 video1 funny videos file4 3 1080 MP4 1 video1 funny videos file5 2 1080 MP4 1 video1 funny videos file6 1 1080 MP4 1 video1 funny videos file7 3 1080 WebM 1 video1 funny videos file8 2 1080 WebM 1 video1 funny videos file9 1 1080 WebM 1 video1 funny videos file10 3 720 WebM 1 video1 funny videos file11 2 720 WebM 1 video1 funny videos file12 1 720 WebM </code></pre> <p>This is an example output. What I would think would be much more effective is to group the results so that you get no redundant information from row to row, after all, you are going to group this information in PHP or the language you are using to be able to use it at all.</p> <p>Example of what I believe would be more effective:</p> <pre><code>| id | name | playlist | file | quality | size | type | --------------------------------------------------------------------- 1 video1 funny videos file1 3 1080 WebM file2 MP4 file3 720 WebM file4 MP4 file5 2 1080 WebM file6 MP4 file7 720 WebM file8 MP4 file9 1 1080 WebM file10 MP4 file11 720 WebM file12 MP4 </code></pre> <p>This way you can easily set up some variables corresponding to the column names:</p> <pre><code>id, name, playlist, file, quality, size, type while (record = fetchRecord) id = record.id or id name = record.name or name playlist = record.playlist or playlist file = record.file or file quality = record.quality or quality size = record.size or size type = record.type or type // Now you have filled the variables with the exact information you had in the first recordset example, but without wasting so much unnecessary bandwidth and processing power. </code></pre> <p>Now, I'm adding in another table with notes that pop up during the video playback, so if there are x number of notes on a video, there will be 12x rows returned per video.</p> <p>This is insane! Have I missed something? Is what I'm "explaining" here actually possible to do? Or is it better to make one query returning 12 rows per video like it is today, and run through each of the videos fetching the notes, or what?</p> <p>This is the end of my question, thank you very much for reading this all the way through!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload