We help IT Professionals succeed at work.
Get Started

Is my table structure correct?

Rob asked
Last Modified: 2021-04-21
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
1 | medical
2 | karate
3 | Technical
4 | Javascript
5 | Shopping

For the resultant table, my initial structure:
URL & TAG_ID are a UNIQUE combination
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 :)
Watch Question
This problem has been solved!
Unlock 2 Answers and 15 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE