So I'm trying to work out the best table structure and how to interface with the data...
I want to store in a database a list of urls and "tag" them with multiple pre-defined keywords for me to search on later. The tags on the urls may need to be updated from time to time. ie it's not a one off tagging but an on-going review of each URL. More tags may be added in the future and I may need to go back to the urls to re-tag them.
I have the list of predefined tags/keywords in a table and I'm now trying to work out what the structure of the resultant table should be in order for it to be easy to update/add/remove the tags for any given url but also to be able to search for a url based on a keyword.
So far I have
ID | NAME
1 | medical
2 | karate
3 | Technical
5 | Shopping
For the resultant table, my initial structure:
URL & TAG_ID are a UNIQUE combination
ID | URL | TAG_ID
1 | www.e-e.com
2 | www.e-e.com
3 | www.google.com
4 | www.google.com
5 | www.amazon.com
So you can see there's multiple entries for one url so that there's a one-to-one match with a tag. I'm not sure if this is the best approach?
BTW The urls and tags given above are for example only and haven't determined the ones I'll use yet. I'm more interested in the structure I would use.
If the above table structure is sound, then how do I go about adding/removing tags? The server will receive a set of tags from the front end... Can MySQL do a diff and only add/remove those records that do / do not match?
I gather for searching it's just a matter of a query/view that joins both tables where a keyword search can be done?
Your help is greatly appreciated :)