troubleshooting Question

Is my table structure correct?

Avatar of Rob
RobFlag for Australia asked on
DatabasesPHPMySQL ServerSQL
15 Comments2 Solutions153 ViewsLast Modified:
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
tags table
ID | NAME
1 | medical
2 | karate
3 | Technical
4 | Javascript
5 | Shopping

For the resultant table, my initial structure:
URL & TAG_ID are a UNIQUE combination
ID | URL | TAG_ID
1 | https://www.experts-exchange.com | 4
2 | https://www.experts-exchange.com | 3
3 | www.google.com | 1
4 | www.google.com | 2
5 | www.amazon.com | 5

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 :)
ASKER CERTIFIED SOLUTION
gr8gonzo
Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros