Hi
I am building a website that needs a Keyword Tag feature added to allow easy and quick searching and I just need some suggestions or thoughts on the best possible method.
An example of my database structure would be as follows:
tbl_films
tbl_directors
tbl_genres
Now against each record that can be saved into each table above, I need to be able to apply keyword tags which are predefined and stored in a separate table called tbl_tags. The user, when creating a new Film record (for example) would choose from the list of predefined tags that should be associated to the record.
Now my questions is to generally find out what the best method would be in storing the tags against the associated records in the 3 tables above.
My first thought was:
Add a 'tags' field to each table (tbl_films, tbl_directors etc) and physically store the keyword tag in a comma separated string against the record and when the user does a search on the site, I would have to search through each table/record looking for a match in the new 'tag' field. The downside to this is what happens if a tag is deleted or renamed in tbl_tags - how do I update my 'tags' field in all of my other tables swiftly!
My second thought was:
To have some kind of Junction table which stores the tbl_tag.id field along with the unique ID from the tbl_films (for example) and then when user searches for a 'keyword' I then have to INNER JOIN this junction table when searching for results to see if any records are associated to the tbl_tags.tag.
If you have any thoughts or suggestions as to what the best approach would be I would be very grateful as it is important that I make the right decision moving forward form this point.
Thanks
kbc
