PHP tag Tag design mode

Source: Internet
Author: User
Tags php tutorial

There is not much time for full-text translation, the focus is singled out, with their own words strung up, called selective translation. It may be more likely to be used in this way later.

The tag storage of social bookmarks has always been a troublesome problem.

A good data table design, not only to be able to accurately identify the tag, but also support tag and/or/not query. Let's take a look at the solution.

"Mysqlicious" solution

Table structure

Storage instance

Intersection (and)

Queries for the "Search+webservice+semweb" class:

SELECT *

From ' Delicious '

WHERE tags like "%search%"

and tags like "%webservice%"

and tags like "%semweb%"

Union (OR)

Queries for the "Search|webservice|semweb" class:

SELECT *

From ' Delicious '

WHERE tags like "%search%"

OR tags like "%webservice%"

OR tags like "%semweb%"

Minus

Queries for the "Search+webservice-semweb" class

SELECT *

From ' Delicious '

WHERE tags like "%search%"

and tags like "%webservice%"

and tags not like "%semweb%"

Advantages:

Only one table SQL comparison directly

Can be done with MySQL full-text search, more efficient

Disadvantages:

The number of tags is limited, usually we use varchar, this field is only 256 bytes long. Otherwise, you need to use the text type and the speed will be slower. (Easy Note, PHP(PHP training PHP Tutorial)More of the tag with is Tinytext)

Like '%things% ' is imprecise, and of course in some applications, this is actually needed

"Scuttle" solution

Data Sheet

Intersection (and)

Query for "Bookmark+webservice+semweb":

SELECT b.*

From Scbookmarks B, sccategories c

WHERE c.bid = B.bid

and (C.category in (' Bookmark ', ' WebService ', ' Semweb '))

GROUP by B.bid

Having COUNT (b.bid) =3

First, all bookmarks-tag combinations are searched (c.category in (' Bookmark ', ' WebService ', ' Semweb ')), and then select which contains three (having COUNT (b.bid) =3)

Union (OR)

Query for "Bookmark|webservice|semweb":

Just need to get rid of it? The HAVING clause in the and query:

SELECT b.*

From Scbookmarks B, sccategories c

WHERE c.bid = B.bid

and (C.category in (' Bookmark ', ' WebService ', ' Semweb '))

GROUP by B.bid

Minus (exclusion)

Query for "Bookmark+webservice-semweb", which Is:bookmark and webservice and not semweb.

SELECT B. *

From Scbookmarks B, sccategories c

WHERE b.bid = C.bid

and (C.category in (' Bookmark ', ' WebService '))

and B.bid not

In (SELECT b.bid from Scbookmarks B, sccategories c WHERE b.bid = c.bid and c.category = ' semweb ')

GROUP by B.bid

Having COUNT (b.bid) =2

The advantage: I think the biggest reason for this scheme is better than the previous one is that there can be unlimited tags.

"Toxi" solution

Data Sheet

Intersection (and)

Query for "Bookmark+webservice+semweb"

SELECT b.*

From Tagmap bt, bookmark B, Tag t

WHERE bt.tag_id = t.tag_id

and (T.name in (' Bookmark ', ' WebService ', ' Semweb '))

and b.id = bt.bookmark_id

GROUP by b.ID

Having COUNT (b.id) =3

Union (OR)

Query for "Bookmark|webservice|semweb"

SELECT b.*

From Tagmap bt, bookmark B, Tag t

WHERE bt.tag_id = t.tag_id

and (T.name in (' Bookmark ', ' WebService ', ' Semweb '))

and b.id = bt.bookmark_id

GROUP by b.ID

Minus (exclusion)

Query for "Bookmark+webservice-semweb", which Is:bookmark and webservice and not semweb.

SELECT B. *

From bookmark B, Tagmap bt, Tag t

WHERE b.id = bt.bookmark_id

and bt.tag_id = t.tag_id

and (T.name in (' Programming ', ' algorithms '))

and b.ID not in (SELECT b.id from bookmark B, Tagmap bt, tag t WHERE b.id = bt.bookmark_id and bt.tag_id = t.tag_id and T. name = ' Python ')

GROUP by b.ID

Having COUNT (b.id) =2

Leaving out thehaving Countleads to the Query for "Bookmark|webservice-semweb".

Benefits:

You can add additional information to each tag .

This is the most canonical scenario, the third paradigm.

Harm:

When you delete tags, you want to remove them from multiple tables (easy note, MYSQL5, can be done with trigger)

We then shifted our gaze from function to performance.

A+b

250 Tags

999 tags

A OR B

250 tags

Add Speed Comparison

Test code download? Download the source code (PHP) LGPL protocol.

PHP tag Tag design mode

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.