Don't know the right way on mysql

Question

I am going to make a website and before that I am going to make a database table. The problem is how to do it correctly. Sorry, but I am new to PHP and Mysql. I only have a definite number of categories, so this is my DB view:

--------------------
Video_name   Varchar
Video_loc    Varchar
cat1         Boolean
cat2         Boolean
cat3         Boolean
cat4         Boolean
cat5         Boolean
cat6         Boolean
cat7         Boolean
--------------------

I kept it like this because 1 video might belong to 2 or more categories. But it will take a lot of space in long term. If a video belongs to cat2 & cat5 then the boolean will have the value "1". But others will also have the value "0" and this is a waste of space.

How can I do it the right way? BTW I have seen Wordpress, it gives the categories a unique ID then on the main table it specifies multiple ids for multiple categories like e.g.: it keeps it like:

-----------------------
id | title | category |

1  | Lorem | 2,4,8,16 |
2  | Epsum | 2,9,8,20 |
3  | Losum | 2,4,9,5  |
4  | Eprum | 2,10,8,1 |
-----------------------

I don't get how it did it like that. Can anyone tell me how to do it like this or any better idea than mine?

Answer 1

Generally it is a "no no" to store structured data within a single column in a table. The Wordpress example does exactly that; it stores a comma separated list of categories in the category column. For that reason I would not recommend using that schema.

The "right" way to do this is to have a table for videos and another table for categories. The relationship between videos and categories is managed by a third table which maps the video to a category and vice versa. There will be multiple entries in this video-to-category table - one for each category that a video can be assigned. Something like this:

table: video
----------------
id       int
name     varchar
location varchar
----------------

table: category
----------------
id       int
name     varchar
----------------

table: video_category
---------------------
video_id          int
category_id       int
---------------------

Another way would be to use a bit field for the categories. Your video table would then have just a single column for all categories with bits set to 1 for those categories to which the video belongs, and 0 for those that it does not. This is an option if storage space is very important to you, however, it is less flexible than using 3 tables, and I recommend the latter approach.

Answer 2

Generally you use a 3rd table with the relationship between the video and category. So you'd have a categories_videos table which has id, category_id, and video id

You can do joins to get all categories related to material, but this is more advanced MySQL. Either start reading the MySQL documentation or pickup a framework that helps you with all this and makes it easier, I always recommend Cakephp.