CODEWITHSUNDEEP
phpmysqldatabasedatabase-designrelational-database
Ben
Ben

Reputation: 16641

Database Design for Video-DB with Playlists

It has been a while since my last database class and I have a few questions about the following basic database design for a PHP/MySQL application that is a video database with playlists:

t_playlists
p_id (PK) | name | description

t_videos
v_id (PK) | name | description | playcount

t_mapping
v_id (FK) | p_id (FK)

The queries I want to perform are as follows:

Currently, one video can only belong to one playlist.

Now to my questions:

  1. Could you remind me of why we use a mapping table in such a case? I recall that it's "good practice", but I don't recall exactly why. How is using a separate mapping table "better" than including the p_id as another column (FK) in t_videos? Wouldn't that make my queries a lot simpler?
  2. Is there a more efficient design that achieves the same?
  3. What are the SQL queries for the three cases I have described? (I am using MySQL)

Upvotes: 0

Views: 1129

Answers (1)

Leo Correa
Leo Correa

Reputation: 19789

  1. If you currently have a One to Many relationship between videos and playlists then there is no need to the mapping table just have the p_id (FK) in the t_videos. If you were to have a Many to Many relationship then you would need the mapping table. You are right since in your case you have a One to Many, having the p_id in the t_videos table would make the query simpler.

  2. Like I said, unless you have a many to many there is no need for the mapping table. As for a more efficient design, the concept you have is very simple and so is the design. Not really sure if it could get more efficient than that.

3.The first one needs a nested statement to get a count of the videos so it would go something like. 

SELECT "name", description, (SELECT COUNT(*) FROM t_videos WHERE fk_p_id = p_id) AS VideoCount FROM t_playlists

That will display the count for each playlist in a column called VideoCount

SELECT * FROM t_videos WHERE fk_p_id = given_p_id

(This is if you were to drop the mappings table and include the p_id in the videos table

SELECT * FROM t_playlists 
INNER JOIN t_videos
ON t_playlists.p_id = t_videos.fk_p_id 
WHERE v_id = given_v_id

Note that I put * instead of the column names for simplicity, but you should include the column names that you want to display only.

EDIT: This is all assuming you have a one to many which is what you stated. It seems to me that the relationship should be many to many though.

Upvotes: 1

Related Questions