Best Practice Question - Modeling TAGS/LABELS/KEYWORDS in SQL RRS feed

  • Question

  • What's the best way to implement a data model supporting tags? 

    We're going to be allowing users to use pre-created or custom created tags (labels or keywords) they can add to their contact data. Things like "Call Monday", "Knows Susan", "Hot Prospect", or similar. These tags will have to be displayed on every contact page, and are fully searchable (show me all my contacts that are tagged "Hot Prospect" and "Call Monday").

    We have a huge database. 

    Is it best to have a tag table (tagid, memberid, tagdesc) and a contacttag table (contactid, tagid) or use a contact.tag_list column with full text search? 

    Looking for advice on performance.

    Monday, February 22, 2010 5:18 PM

All replies

  • First off I think this probably should go in another forum as I don't see anything about spatial data, but here's what I could give you anyway. I'm keeping the tables pretty basic and I doubt this will run, but hopefully you get the idea.

    create table Contact
    ( contactid int not null primary key identity,
    contactname varchar(100) not null)

    create table ContactTag
    ( contacttagid int not null primary key identity,
    contactid int not null references Contact(contactid),
    contacttagtext (varchar(100) not null)

    Something like that should work. Pretty straight forward one to many, slap a fulltext on both, and you're good to go.  

    Monday, February 22, 2010 6:47 PM