I don't want to lose this thread because I think there are still some ideas to continue flushing out. More comments inline ...
On Fri, 2006-01-13 at 05:14, David Levy wrote: > Sorry to have taken so long. > > The denormalisations of the author_name (which may be owner name) and > entrydate are to support queries. This is because I expect a macro to > create a tag cloud for a user so that the html versions can have the tag > cloud, > > So > > select normal_tag_name, count(*) > from entry2tags > where author_name = "DaveLevy" > group by normal_tag_name > > gives us the data required for a tag cloud, for a single blog . No join > as you can see, where it gets fun is if you want a hot tags cloud > > we add a line so the query becomes > > select normal_tag_name, count(*) > from entry2tags > where author_name = "DaveLevy" > and entry_date > @sevedaysago > group by normal_tag_name very cool stuff ... i like the looks of that. ... lots of stuff chopped out here ... > > >>is there a reason to copy down the entry_date rather than access it via a > >>join on the weblogentry table? you can join with the weblogentry table > >>using the entry_id column. how were you planning to use the entry_date > >>field? > >> > >> > see above, I don't want to join to the entry table, and this goal also > impacts my index design. entry_date allows hot tags queries to be > driven by the entry not the tagged date ok. I agree that joins are a likely performance problem, but my next question then becomes ... How do we plan to deal with getting the data for the list of entries marked with a given tag? I am expecting that when someone uses the tag dashboard or a tag cloud to try and view a list of entries with the tag "foo", that list will look something like the Roller front page. example ... url = /roller/tag/entries/java+netbeans you then populate a page with 50? 25? entry summaries for people to browse through and those summaries will at least require the entry title and a summary of the entry content and may also require the entry date, category, author, and weblog title. I would think we are going to require a join to get that data. > > >>what acts as the primary key? (author_name, entry_id, user_tag_name)? > >> > my PK is author_name, entry_id and normal_tag_name > > >>we may still need to use a surrogate key to uniquely identify the row to > >>avoid having a multi column primary key. > >> > yeah, looks like it > > >> i'm not sure that would be much of an issue though because it doesn't look > >> like you are planning for any joins for the tag names, correct? > >> > >> > >> > I'm trying to avoid any joins, but if you are looking for entries on a > blog and tagged, then it would be good to enter the query on > author_name, but since we have not copied the title down to the > entry2tags table we still need the join and can go in on author_name on > either table, but best do it on entry table (see below). > > select entry.title > from entry, entry2tags e2t > where e2t.entry_id = entry.id > and entry.author_name = @KnownName > and e2t.normal_tag_name in (@TagQueryList) based on my example above, how would we get the necessary entry data when we don't know the author name because we are searching through the entire tag system, not just from a single author or weblog? -- Allen > > > > >Right, Allen. This is very similar to what we already have. I'm not sure > >why having author name here, when we already have that through a join > >with the entry_id. > > > That's right, but I don't want to join, the entry2tags table is big > enough without joining. > > > I'm not sure why do we need entry_date when we have > >the tagging date. > > > I think the queries should be driven through the entry publication date. > > >I do like the normal tag name. I was thinking this too for the output of the > >Porter Stemming algorithm so I wouldn't lose the original information > >entered by the user. Everything else is the same :-). > > > > > I need to read your references to understand this, but I think you agree > this is OK > > >Another point I want to make is the fact, that we do a little bit of > >extra processing when saving an existing entry that make sure it keeps > >the original date for each tag. For example, when I first created an > >entry I tagged it A,B,C. The first time I edited, I removed B. The A and > >C tags will retain the dates when they were added as opposed to the edit > >date. > > > > > Are you holding entry_tag data on the entry table? > > >Additionally, we have a question of what to do with spaces. Should tags > >be multi-word or not? My suggestion to Phay (one of our developers) was > >to use spaces as separators in the input field, therefore not supporting > >spaces. But we could do multiple things to support spaces, such as > >quoting multi-word tags. I believe Flickr supports multi-words but they > >remove the spaces from the tags, but technorati does maintain spaces. I > >don't like them myself, because I think it fragments the tag space much > >more than single words and you could still use intersections to get the > >sort of the same result. > > > > > > > > > >>>create constraint defined_tags_name as > >>> normal_tag_name=proper(user_tag_name) > >>> > >>>create constraint defined_tags_entry_date as > >>> entry_date = select entry_date from entry > >>> where definedtags.entry_name = entry.entry_name > >>> > >>>and the following indexes > >>> > >>>create dt.tags on definedtags > >>> as author_name, entry_name, normal_tag_name unique > >>> > >>> > This is the real primary key > > >>>create dt.tags2 on definedtags > >>> as normal_tag_name > >>> > >>> > This is the tag entity (or operational master) > > >>>create dt.entries on definedtags > >>> as author_name, entry_name > >>> > >>>create dt.date_written > >>> on definedtags as entry_date. > >>> > >>>I have written this in a hurry so it may not be though out as well as > >>>some of my writing, but hopefully this is collaborative development. > >>>Also I have difficulty in commenting on and reading some of the > >>>front-end & java orientated stuff. (I have ordered a couple of books to > >>>help me catchup). Hopefully this is helpfull > >>> > >>> > >>> > >>I think this all makes sense to me so far and I certainly appreciate the > >>help. I think getting the data model correct is a *very* important issue > >>before we move forward with implemenatation, so I'm glad we are having this > >>discussion. > >> > >>-- Allen > >> > >> > > > >[1] http://www.tartarus.org/~martin/PorterStemmer/def.txt > >[2] http://torrez.us/archives/2005/07/13/tagrank.pdf > > > > > > > >>>Elias Torres wrote: > >>> > >>> > >>> > >>>>Welcome David to the Roller list. > >>>> > >>>>Thank you for your post. I have read your blog post on a tag data > >>>>model for Roller. I'm looking forward to your relational algebra and > >>>>query cost analysis. I wanted to tell you that we (IBM) have already > >>>>added basic tagging support to Roller and it actually supports a > >>>>TagCloud. I am supposed to put a proposal in the roller wiki so others > >>>>could comment and once I do that, you could put your comments there as > >>>>well. > >>>> > >>>>Just to kickstart the conversation I'm including the tagging table we > >>>>are currently using. > >>>> > >>>>create table weblogentrytag ( > >>>> id varchar(48) not null primary key, > >>>> entryid varchar(48) not null, > >>>> name varchar(255) not null, > >>>> tagtime timestamp not null > >>>>); > >>>> > >>>>We have basically two tables: entries and entry2tags, but are missing > >>>>a tag table. At first, I was very set on having a tag table and use a > >>>>foreign key to "save" space on repetitive tag names. But I was shown > >>>>it's not really a big space saving technique, especially since tag > >>>>names are relatively short storing a guid or int would almost be > >>>>comparable in space. There are also increased costs in inserting and > >>>>joining on tables to get tag names if using a foreign key, so we have > >>>>settle on this for now until we have other queries requirements. I'll > >>>>be summarizing all of our changes to roller to support tagging in a > >>>>wiki proposal soon. > >>>> > >>>>Regarding the use of the list, some people have been using nabble.com > >>>>to interact with it. Maybe you can give it a try. I simply use gmail. > >>>> > >>>>http://www.nabble.com/Roller-f12275.html > >>>> > >>>>Regards, > >>>> > >>>>Elias > >>>> > >>>>On 1/4/06, David Levy <[EMAIL PROTECTED]> wrote: > >>>> > >>>> > >>>> > >>>> > >>>>>I have documented a data model for tags. This is held at my blog > >>>>> > >>>>>http://blogs.sun.com/roller/page/DaveLevy?entry=implementing_tags_in_a_database > >>>>> > >>>>>I have a graphic demonstrating the relationship between authors, > >>>>>articles and tags and illustrating the first and obvious indexes. (I > >>>>>have identified that both "Date Published" and tag aggregates are > >>>>>missing from the model). Since the model was built to help me > >>>>>understand del.icio.us, I call the entities Users, Bookmarks and Tags, > >>>>>but hopefully its simple to see that these are pretty synonomous to > >>>>>authors, articles and tags. > >>>>> > >>>>>I hope that this is useful for those looking at implementing tags. > >>>>> > >>>>>I am still working out how to use the mail-list, so I hope that x-refing > >>>>>you to my blog isn't deprecated. I also need to work how to maintain > >>>>>thread connections i.e. undertake a reply. > >>>>>-- > >>>>> > >>>>>Dave > >>>>> > >>>>> > >>>-- > >>> > >>>Dave > >>> > >>> > >>> > >> > >> > > -- > > Dave > > <http://www.sun.com> * David Levy * > > > > > > > Blog http://blogs.sun.com/DaveLevy > Email [EMAIL PROTECTED] > > Sun Proprietary & Confidential . This e-mail message is for the sole use > of the intended recipient(s) and may contain confidential and > privilidged information. Any unauthorised review, use, disclosure or > distribution is prohibited. If you are not the intended recepient, > please contact the sender by reply e-mail and destroy all copies of the > original message. >
