태깅을위한 데이터베이스 디자인

태깅을위한 데이터베이스 디자인

---

다음과 같은 태깅 기능을 지원하기 위해 데이터베이스를 어떻게 설계 하시겠습니까?

• 항목에 많은 수의 태그가있을 수 있습니다
• 지정된 태그 세트로 태그가 지정된 모든 항목을 빠르게 검색해야합니다 (항목에 모든 태그가 있어야하므로 OR 검색이 아닌 AND 검색 임)
• 빠른 검색 / 읽기를 가능하게하기 위해 항목 작성 / 쓰기 속도가 느려질 수 있습니다.

이상적으로는 (적어도) n 개의 지정된 태그 세트로 태그가 지정된 모든 항목의 조회는 단일 SQL 문을 사용하여 수행해야합니다. 검색 할 태그의 수와 모든 항목의 태그 수는 알 수없고 높을 수 있으므로 JOIN을 사용하는 것은 실용적이지 않습니다.

어떤 아이디어?

---

지금까지 모든 답변에 감사드립니다.

그러나 내가 실수하지 않으면 주어진 답변은 태그에 대한 OR 검색을 수행하는 방법을 보여줍니다. 하나 이상의 n 태그가있는 모든 항목을 선택하십시오. 효율적인 AND 검색을 찾고 있습니다. (모두 n 개 이상의 태그가있는 항목을 모두 선택하십시오.)

---

ANDing 정보 : "관계 구분"작업을 찾고있는 것 같습니다. 이 기사 는 간결하면서도 이해하기 쉬운 관계 구분을 다룹니다.

성능 정보 : 비트 맵 기반 접근 방식은 상황에 가장 적합한 것처럼 직관적으로 들립니다. 그러나 digiguru가 제안한 것처럼 비트 맵 인덱싱을 "수동으로"구현하는 것이 좋은 생각이라고 확신하지 못합니다. 새로운 태그가 추가 될 때마다 복잡한 상황처럼 들립니다 (?) 그러나 일부 DBMS (Oracle 포함)는 어떻게 든 비트 맵 인덱스를 제공합니다 내장 인덱싱 시스템은 인덱스 유지 관리의 복잡성을 없애기 때문에 사용 중입니다. 또한 비트 맵 인덱스를 제공하는 DBMS는 쿼리 계획을 수행 할 때 비트 맵 인덱스를 적절하게 고려할 수 있어야합니다.

---

다음은 데이터베이스 스키마 태그 지정에 대한 좋은 기사입니다.

http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

성능 테스트와 함께 :

http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/

MySQL에 대한 결론은 (최소한 작성 당시 2005 년에) 전체 텍스트 인덱싱 특성이 매우 낮다는 결론을 내 렸습니다.

---

간단한 솔루션에는 문제가 없습니다. 항목 용 테이블, 태그 용 테이블, "태깅"을위한 크로스 테이블

크로스 테이블의 인덱스는 충분히 최적화되어야합니다. 적절한 항목을 선택하면

SELECT * FROM items WHERE id IN  
    (SELECT DISTINCT item_id FROM item_tag WHERE  
    tag_id = tag1 OR tag_id = tag2 OR ...)  

AND 태깅은

SELECT * FROM items WHERE  
    EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag1)  
    AND EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag2)  
    AND ...

많은 수의 비교 태그에는 그렇게 효율적이지 않습니다. 메모리에서 태그 수를 유지해야하는 경우 자주 그렇지 않은 태그로 시작하도록 쿼리를 만들 수 있으므로 AND 시퀀스가 ​​더 빨리 평가됩니다. 일치하는 예상 태그 수와 단일 태그 일치에 대한 기대에 따라 20 개의 태그를 일치시키고 임의의 임의 항목이 15 개와 일치 할 것으로 예상하면 괜찮은 해결책이 될 수 있습니다. 데이터베이스에서.

---

@Jeff Atwood ( http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/ )에 링크 된 기사 가 매우 철저 하다는 사실을 강조하고 싶었습니다. ( 3 가지 다른 스키마의 장점에 대해 설명합니다. 그리고 지금까지 언급 한 것보다 일반적으로 더 나은 성능을 제공하는 AND 쿼리에 대한 좋은 솔루션을 제공합니다 (즉, 각 용어에 대해 상관 된 하위 쿼리를 사용하지 않음). 또한 의견에 좋은 것들이 많이 있습니다.

추신-여기에 모든 사람들이 이야기하는 접근 방식을 기사에서 "Toxi"솔루션이라고합니다.

---

Java 컨텐츠 리포지토리 구현 (예 : Apache Jackrabbit )과 같은 엄격하지 않은 데이터베이스 솔루션을 실험하고 Apache Lucene 과 같은 기반으로 구축 된 검색 엔진을 사용할 수 있습니다 .

적절한 캐싱 메커니즘을 갖춘이 솔루션은 자체 개발 솔루션보다 더 나은 성능을 제공 할 수 있습니다.

그러나 실제로 중소 규모의 응용 프로그램에서는 이전 게시물에서 언급 한 표준화 된 데이터베이스보다보다 정교한 구현이 필요하다고 생각하지 않습니다.

편집 : 명확하게하면 검색 엔진과 함께 JCR 유사 솔루션을 사용하는 것이 더 매력적입니다. 그것은 장기적으로 프로그램을 크게 단순화시킬 것입니다.

---

가장 쉬운 방법은 tags 테이블 을 만드는 것 입니다.
Target_Type-여러 테이블에 태그를 지정할 경우
Target-태그가 지정된 레코드의 키
Tag- 태그 의 텍스트

데이터 쿼리는 다음과 같습니다.

Select distinct target from tags   
where tag in ([your list of tags to search for here])  
and target_type = [the table you're searching]

업데이트
AND 조건에 대한 요구 사항에 따라 위의 쿼리는 다음과 같이 나타납니다.

select target
from (
  select target, count(*) cnt 
  from tags   
  where tag in ([your list of tags to search for here])
    and target_type = [the table you're searching]
)
where cnt = [number of tags being searched]

---

나는 (Z) DB 중심이 아닌 것을 원할지도 모른다는 @Zizzencs의 두 번째 제안

어떻게 든 일반 nvarchar 필드를 사용하여 적절한 캐싱 / 인덱싱으로 태그를 저장하면 더 빠른 결과를 얻을 수 있다고 생각합니다. 그러나 그것은 단지 나입니다.

I've implemented tagging systems using 3 tables to represent a Many-to-Many relationship before (Item Tags ItemTags), but I suppose you will be dealing with tags in a lot of places, I can tell you that with 3 tables having to be manipulated/queried simultaneously all the time will definitely make your code more complex.

You might want to consider if the added complexity is worth it.

---

You won't be able to avoid joins and still be somewhat normalized.

My approach is to have a Tag Table.

 TagId (PK)| TagName (Indexed)

Then, you have a TagXREFID column in your items table.

This TagXREFID column is a FK to a 3rd table, I'll call it TagXREF:

 TagXrefID | ItemID | TagId

So, to get all tags for an item would be something like:

SELECT Tags.TagId,Tags.TagName 
     FROM Tags,TagXref 
     WHERE TagXref.TagId = Tags.TagId 
         AND TagXref.ItemID = @ItemID

And to get all items for a tag, I'd use something like this:

SELECT * FROM Items, TagXref
     WHERE TagXref.TagId IN 
          ( SELECT Tags.TagId FROM Tags
                WHERE Tags.TagName = @TagName; )
     AND Items.ItemId = TagXref.ItemId;

To AND a bunch of tags together, You would to modify the above statement slightly to add AND Tags.TagName = @TagName1 AND Tags.TagName = @TagName2 etc...and dynamically build the query.

---

What I like to do is have a number of tables that represent the raw data, so in this case you'd have

Items (ID pk, Name, <properties>)
Tags (ID pk, Name)
TagItems (TagID fk, ItemID fk)

This works fast for the write times, and keeps everything normalized, but you may also note that for each tag, you'll need to join tables twice for every further tag you want to AND, so it's got slow read.

A solution to improve read is to create a caching table on command by setting up a stored procedure that essentially creates new table that represents the data in a flattened format...

CachedTagItems(ID, Name, <properties>, tag1, tag2, ... tagN)

Then you can consider how often the Tagged Item table needs to be kept up to date, if it's on every insert, then call the stored procedure in a cursor insert event. If it's an hourly task, then set up an hourly job to run it.

Now to get really clever in data retrieval, you'll want to create a stored procedure to get data from the tags. Rather than using nested queries in a massive case statement, you want to pass in a single parameter containing a list of tags you want to select from the database, and return a record set of Items. This would be best in binary format, using bitwise operators.

In binary format, it is easy to explain. Let's say there are four tags to be assigned to an item, in binary we could represent that

0000

If all four tags are assigned to an object, the object would look like this...

1111

If just the first two...

1100

Then it's just a case of finding the binary values with the 1s and zeros in the column you want. Using SQL Server's Bitwise operators, you can check that there is a 1 in the first of the columns using very simple queries.

Check this link to find out more.

---

To paraphrase what others have said: the trick isn't in the schema, it's in the query.

The naive schema of Entities/Labels/Tags is the right way to go. But as you've seen, it's not immediately clear how to perform an AND query with a lot of tags.

The best way to optimize that query will be platform-dependent, so I would recommend re-tagging your question with your RDBS and changing the title to something like "Optimal way to perform AND query on a tagging database".

I have a few suggestions for MS SQL, but will refrain in case that's not the platform you're using.

---

A variation to the above answer is take the tag ids, sort them, combine as a ^ separated string and hash them. Then simply associate the hash to the item. Each combination of tags produces a new key. To do an AND search simply re-create the hash with the given tag ids and search. Changing tags on an item will cause the hash to be recreated. Items with the same set of tags share the same hash key.

---

If you've an array type, you can pre-aggregate the needed data. See this answer in a separate thread:

what's the utility of array type?

참고URL : https://stackoverflow.com/questions/48475/database-design-for-tagging