CodingForums.com - View Single Post - Tagging system set up

shaunthomson · 02-07-2013, 04:03 AM

Cheers Old Pedant

Why would I need to delete everything if something changed? If I needed to add or delete a tag in the future, can I not just add or delete a column, and amend my php query?

What is "Toxi"? Where did that come from?

I found it in my research here:
http://tagging.pui.ch/post/370277457...tabase-schemas

I would like to point out that if you do denormalize then there are several possible future queries that get tougher. Example:
"How many bookmarks use both tag13 and tag93?"
[WHERE (tag1=13 AND tag2=93) OR (tag1=93 AND tag2=13)]
[ugh!]

I'm misunderstanding why I would need that query? If a user can only search by one tag eg 13, wouldn't...

WHERE BookmarkID = $bookmark
AND Tag1 = 13 OR Tag2 = 13

...do the job?

"How many bookmarks have only one tag?"
[WHERE (tag1 IS NOT NULL AND tag2 IS NULL) OR (tag1 IS NULL AND tag2 IS NOT NULL]
[double ugh!]

...I'm thinking that if only one tag is inserted, I would always make sure it's in `tag1`, so again, wouldn't a search like above for 13 do the job?

"How many bookmarks use exactly the same two tags as another bookmark?"
[much more code than I want to show]
I'm unsure what this means - are you getting at the fact that there would be double ups of the same tag pairs, so therefore extra data? Wouldn't this be offset by the fact that a "Toxi" type system would introduce roughly the same amount of extra data.

I'm not meaning to be argumentative at all - just trying to understand the pros and cons, and pick the right system to give me the FASTEST results when querying.

I would go for the "Toxi" type system, only that if would introduce another join into my queries that already have have one or sometimes two, so I'm researching to see which would be faster - another join, or a denormalized tag system.

To round my questions out, I have other tagging conditions to add:

When searching, user gets to choose...

- one tag from condition one eg 15 (bookmark has two tag columns for this)
- one tag from condition two eg 19(bookmark has one tag column for this)
- one tag from condition three eg 2 (bookmark has one tag column for this)

(queries would be dynamically built to include or exclude ANDs based on what conditions user choose to include)

So what would return faster results...?

WHERE bookmarkID = 456
AND tagCond1A = 15 OR tagCond1B = 15
AND tagCond2 = 19
AND tagCond3 = 2

or a join query with the "Toxi" type tag system?

Speed of results is key for me.

Thanks!!!

02-07-2013, 04:03 AM	PM User \| #3
shaunthomson New Coder Join Date: May 2012 Posts: 89 Thanks: 51 Thanked 0 Times in 0 Posts	Cheers Old Pedant Why would I need to delete everything if something changed? If I needed to add or delete a tag in the future, can I not just add or delete a column, and amend my php query? What is "Toxi"? Where did that come from? I found it in my research here: http://tagging.pui.ch/post/370277457...tabase-schemas I would like to point out that if you do denormalize then there are several possible future queries that get tougher. Example: "How many bookmarks use both tag13 and tag93?" [WHERE (tag1=13 AND tag2=93) OR (tag1=93 AND tag2=13)] [ugh!] I'm misunderstanding why I would need that query? If a user can only search by one tag eg 13, wouldn't... WHERE BookmarkID = $bookmark AND Tag1 = 13 OR Tag2 = 13 ...do the job? "How many bookmarks have only one tag?" [WHERE (tag1 IS NOT NULL AND tag2 IS NULL) OR (tag1 IS NULL AND tag2 IS NOT NULL] [double ugh!] ...I'm thinking that if only one tag is inserted, I would always make sure it's in `tag1`, so again, wouldn't a search like above for 13 do the job? "How many bookmarks use exactly the same two tags as another bookmark?" [much more code than I want to show] I'm unsure what this means - are you getting at the fact that there would be double ups of the same tag pairs, so therefore extra data? Wouldn't this be offset by the fact that a "Toxi" type system would introduce roughly the same amount of extra data. I'm not meaning to be argumentative at all - just trying to understand the pros and cons, and pick the right system to give me the FASTEST results when querying. I would go for the "Toxi" type system, only that if would introduce another join into my queries that already have have one or sometimes two, so I'm researching to see which would be faster - another join, or a denormalized tag system. To round my questions out, I have other tagging conditions to add: When searching, user gets to choose... - one tag from condition one eg 15 (bookmark has two tag columns for this) - one tag from condition two eg 19(bookmark has one tag column for this) - one tag from condition three eg 2 (bookmark has one tag column for this) (queries would be dynamically built to include or exclude ANDs based on what conditions user choose to include) So what would return faster results...? WHERE bookmarkID = 456 AND tagCond1A = 15 OR tagCond1B = 15 AND tagCond2 = 19 AND tagCond3 = 2 or a join query with the "Toxi" type tag system? Speed of results is key for me. Thanks!!! Last edited by shaunthomson; 02-07-2013 at 03:50 PM..