I am using PHP and MySQL to manage pages on my web site. I have a custom made content management system.
I have a question... let's say we have the table "pages" with all of the good stuff. Focus specifically on column "page_category". Let's say there's another table that contains a list of all of the categories.
What would be the most efficient way to go about allowing a page to belong to multiple categories? Right now, I am using a comma delimited mechanism (i.e. if column "page_category" is "1,4", the page belongs to the respective category IDs 1 and 4). However, I am getting a little tired of the FIND_IN_SETS() and was wondering if there was a better way to do it. implode() and explode() is also annoying.
Also, when displaying a list of pages, if my "page_category" column contains "1,4" what would be the most efficient way, through MySQL, to join the results to the "categories" table (because "1, 4" looks really dumb when the results are displayed; I want the "category_name")?
Are there any other better application theories out there? Let me know.