So, me and a friend of mine were throwing around ideas on how to store a list of 39 genres (like Action, Adventure) and not make it a taxing task on the database. My idea was to have a list of 39 columns with the layout being, Show_Name, Action, Adventure... (Etc) Star Trek, 1 Code (markup): So you would have a 1 value in the genre that it is and then do a query like "SELECT Star Trek WHERE Action = '1'" (That may not work I just came up with it on the spot) Now here is his idea that I need help with on the math. He suggests that I use a list of numbers like this... [COLOR=#000000][FONT=Segoe UI]0002, 0004, 0008, 0020, 0040, 0080, 0200, 0400, 0800, 2000, 4000, 8000 [/FONT][/COLOR] Code (markup): ... And give each genre a value so that if they select multiple genres it will never add up to another one. (Example: If they select 0002 and 00020 and 0008 the value would equal 0030. We avoid using 30 so each combination would have its own number I guess) Well we are debating on what is better and if we do go with Idea #2 what the numbers would be. I am sure there is some math way to figure it out that I am unaware of so I'm counting on you If you can think of a better way to do it I would love to hear it! Thanks!
Seeing that this is a forum about databases, how about using a relational database to handle this? Have a table of movies, a table of genres and then a table that contains every combination of movies and genres that exist. So "Star Trek" might have 6 rows on the third table whereas "Star Wars" might have 8. You're going to get into a big mess if you use comma separated flat files and huge numbers to identify a genre.
+1. This would be the normalized and rational way to store the data. With the proper foreign keys this would be very efficient.
39 genres is a TRIVIAL database. Look at google with BILLIONS of genres. Don't use numbers. You will need a table just to keep track of them. For a small database like this a flat file, though inefficient and hard to work with, would work. Best thing to do (as noted above) is to set up two tables, one for genres, and one for the movie info, then link them with an index key.
Thanks for all the answers. I am fairly new to PHP and MySQL in whole and just learning the ropes but will try to go over what you guys suggested and come up with a database.
The numbers are powers of 2 - 1, 2, 4, 8, 16, 32, 64, etc. Since you need 39 distinct values, you need 39 numbers - up to 549,755,813,888. It can be done, and PHP has bitwise operators so you can easily insert and select what you want.