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?
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:
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.
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.