CODEWITHSUNDEEP
phpmysql
user1382785
user1382785

Reputation: 3

Reusing keywords for search

I have to make a search feature where the user searches tags which are appended to different articles, like: PC, health, fashion clothing... etc. My current table is like this:

Tag_id  | Tag            | Article_id
-----------------------------------
1       | scary          |   2
-----------------------------------
2       | daunting       |   2
-----------------------------------
3       | chilling       |   2
-----------------------------------
4       | creepy         |   2
-----------------------------------
5       | scary          |   5
-----------------------------------
6       | daunting       |   5
-----------------------------------
7       | chilling       |   5
-----------------------------------
8       | creepy         |   5

As you can see there are synonyms for each tag, which have to be repeated at every article which share the same tags. My question is this: what table structure would allow tags to be reused (no repeated tags), and at the same time not search friendly. (a normal thesaurus can't be used, because the tags are something short phrases and slang)

Upvotes: 0

Views: 45

Answers (2)

Nicolás Torres
Nicolás Torres

Reputation: 1345

ARTICLE TABLE

-id -- UNIQUE (auto incremental or not, you decide)

-More attributes...

TAGS TABLE

-id -- UNIQUE (auto incremental or not, you decide)

-tag

Relational table ARTICLE_TAG TABLE

-article_id

-tag_id

You can relate as many tags with articles you want

Upvotes: 0

Nathaniel Ford
Nathaniel Ford

Reputation: 21239

You want three tables:

ARTICLE
id   (a primary uid)
link (links to the article)

TAG
id   (a primary uid)
name (ie 'chilling')

ARTICLETAG
article (a foreign key: a uid that exists in ARTICLE)
tag     (a foreign key: a uid that exists in TAG)

Each time you want to tag an article, you insert into ARTICLETAG a new row with the article you want to tag and the id of the tag you want to tag it with. Add to TAG and ARTICLE as needed for new articles and tags.

Upvotes: 1

Related Questions