Geek asks and answers

Tag System: Toxi Solution Issues

I’m kind of breaking my head over a Toxi solution for tag database schemas. I am working on a system where users can post items, and these items can have tags associated with them. After reading on tags, I found the Toxi solution most suitable for my needs. However, I'm not quite sure if I plan it right, so I would like you to speak out about this.

I will have three databases.
itemscontaining item_idothers
tagmap, using item_idand tag_idas foreign keys,
tagscontaining tag_idandtag_text

When adding a new item, can I assume that the process of adding tags to the database is as follows:

  • sort submitted tags into an array
  • for each tag in the array:
    • get tag_id from tags, where tag_text matches the current tag
    • if it returns 0 rows:
      • add tag to table tags
      • get tag_id
    • add item_id and tag_id to tagmap
  • complete (give user a-okay, etc.)

This means that we get an entry in the tagmap for each tag for each element. This seems right, but I can't help but think that there is a better way to do this than with a huge amount of entries there ...

Regarding tag editing, I came up with the following process, although I think there is a better way that I haven't found yet.

  • retrieve tags using item_id and paste into user editable field
  • makes changes. on submit:
  • delete lines from tagmap, where item_id corresponds to edited
  • same process as above

. , - , , ? , : tagmap , , , ?

, , , , , . , cron tag_id tagmap , tag_use . , ?

, . Welp, , , , , , , . , , .

!

+3
1

, "" . ! , , .

...

.

, 3 .

...

, , SQL "" . , 1 : "tag1", "tag2" "tag3" :

INSERT IGNORE INTO tagmap (item_id, tag_id)
SELECT 1, tag_id FROM tags WHERE tag_text IN ('tag1', 'tag2', 'tag3');

IGNORE , .

, tags. , tag.tag_id , - , , :

INSERT IGNORE INTO tags (tag_text) VALUES ('tag1'), ('tag2'), ('tag3');

, tagmap . , , , ...

. " " - , , - .

...

( )?

, , :

DELETE FROM tagmap
WHERE
    item_id = 1
    AND tag_id NOT IN (
        SELECT tag_id FROM tags
        WHERE tag_text IN ('tag1', 'tag3')
    );

, 'tag1' 'tag3'. INSERT DELETE , "" , .

SQL Fiddle.

: tagmap , , , ?

. FK (, ON DELETE CASCADE), .

, , tags ( tag_text), ? , , , .

tag_text, , , :

enter image description here

SQL, clustering.

, "" , , ( ). ​​

, , ... cron job...

, - . SQL-, , tagmap PK, , (: InnoDB ). ( ), .

!

+10

More articles:


All Articles