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