SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find similar assets in db by comparing tags
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Snory
Starting Member

USA
2 Posts

Posted - 11/11/2010 :  14:03:02  Show Profile  Reply with Quote
For a CMS project I am working on, I was given the requirement
to find similar assets in the db by comparing tags.

Any time a user visited an asset detail page, they would be
provided with a list of other assets in the db that were
most similar to the one they were currently looking at.

Here is the example I came up with.
I was hoping to get some feedback on this sql.
(or links to other other strategies)

The script will create the tables, insert seed data, and provide several examples of extracting info. You must first create the db.

This script was tested on SQL2005,2008

thanks
Snory




Set Nocount On


/* *************************** MOST POPULAR TAGS *************************** */
/* The goal of this demo is to find out what assets in my database are most  */
/* similar to other assets in the database by comparing tags                 */
/*                                                                           */
/* For this example, there are 3 tables used                                 */
/*    Asset - Contains list of assets, images, objects ...                   */
/*    Tags - Contains a unique list of all tags within the system            */
/*    AssetTagRel - Many to Many rel between assets and tags                 */
/*                                                                           */
/* For seed Data                                                             */
/*    Asset A -> Related to Tags 1,2                                         */
/*    Asset B -> Related to Tags 1,2                                         */
/*    Asset C -> Related to Tags 1,2,3                                       */
/*    Asset D -> Related to Tags 1,3,4                                       */
/*    Asset E -> Related to Tag  5                                           */
/*                                                                           */
/* ************************************************************************* */



/* ************************************************************************* */
/* ************************* DROP PREVIOUS VERSION ************************* */
/* ************************************************************************* */

/* Drop Tables if they already exist */
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AssetTagRel')
	DROP TABLE AssetTagRel
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Assets')
	DROP TABLE Assets
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Tags')
	DROP TABLE Tags
GO

/* ************************************************************************* */
/* ***************************** CREATE TABLES ***************************** */
/* ************************************************************************* */

CREATE TABLE [Assets]
(
	[AssetId] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED 
	(
		[AssetId] ASC
	)
)

CREATE TABLE [Tags]
(
	[TagId] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
	(
		[TagId] ASC
	)
)

CREATE TABLE [AssetTagRel]
(
	[AssetTagRelId] [uniqueidentifier] NOT NULL,
	[AssetId] [uniqueidentifier] NOT NULL,
	[TagId] [uniqueidentifier] NOT NULL,
	CONSTRAINT [PK_AssetTagRel] PRIMARY KEY CLUSTERED ([AssetTagRelId] ASC),
	CONSTRAINT [FK_AssetTagRel_Asset] FOREIGN KEY ([AssetId]) REFERENCES [Assets] ([AssetId]),
	CONSTRAINT [FK_AssetTagRel_Tag] FOREIGN KEY ([TagId]) REFERENCES [Tags] ([TagId])
)

/* ************************************************************************* */
/* ******************************* SEED DATA ******************************* */
/* ************************************************************************* */

/* Seed Data - Assets */
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000A','Asset A')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000B','Asset B')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000C','Asset C')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000D','Asset D')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000E','Asset E')

/* Seed Data - Tags */
Insert Into Tags Values ('00000000-0000-0000-0000-000000000001','Tag 1')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000002','Tag 2')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000003','Tag 3')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000004','Tag 4')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000005','Tag 5')

/* Seed Data - Relationships for Asset A */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000001')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000002')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000003') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000004') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset B */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000001')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000002')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000003') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000004') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset C */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000001')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000002')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000003')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000004') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset D */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000001')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000002') */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000003')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000004')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset E */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000001') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000002') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000003') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000004') */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000005')


/* ************************************************************************* */
/* ******************************* REPORTING ******************************* */
/* ************************************************************************* */

/* List of Assets */
PRINT 'List of Assets'
SELECT
	AssetId,
	Title
FROM
	Assets
ORDER BY
	Title

PRINT ''
PRINT ''

/* List of Tags */
PRINT 'List of Tags'
SELECT
	[TagId],
	[Title]
FROM
	[Tags]
ORDER BY
	Title
	
PRINT ''
PRINT ''
	
/* Display Unique List of Tags that 'Asset A' has in common with 'Asset B' */
PRINT 'Display Unique List of Tags that ''Asset A'' has in common with ''Asset B'''
SELECT TagId FROM AssetTagRel WHERE AssetId = '00000000-0000-0000-0000-00000000000A'
Intersect
SELECT TagId FROM AssetTagRel WHERE AssetId = '00000000-0000-0000-0000-00000000000B'

PRINT ''
PRINT ''
	
/* List all other assets that are in some way related to 'Asset A' */
PRINT 'List all other assets that are in some way related to ''Asset A'''
SELECT a2.AssetId
FROM
	(
		SELECT TagId
		FROM AssetTagRel
		WHERE AssetId = '00000000-0000-0000-0000-00000000000A'
) AS a1
INNER JOIN AssetTagRel a2 ON
	a1.TagId = a2.TagId
	AND AssetId <> '00000000-0000-0000-0000-00000000000A'
GROUP BY
	a2.AssetId

PRINT ''
PRINT ''

/* List all other assets that are the most similar to 'Asset A' sorted by most common relationships / intersections */
PRINT 'List all other assets that are the most similar to ''Asset A'' sorted by most common relationships / intersections '
SELECT a2.AssetId,
	(
		SELECT COUNT(TagId)
		FROM
			(
				select TagId from AssetTagRel where AssetId = a1.AssetId
				Intersect
				select TagId from AssetTagRel where AssetId = a2.AssetId
			) IntersectionList
	)  IntersectionCount
FROM
	(
		SELECT TagId, AssetId
		FROM AssetTagRel
		WHERE AssetId = '00000000-0000-0000-0000-00000000000A'
) AS a1
INNER JOIN AssetTagRel a2 ON
	a1.TagId = a2.TagId
	AND a2.AssetId <> '00000000-0000-0000-0000-00000000000A'
GROUP BY
	a1.AssetId,
	a2.AssetId
Order by IntersectionCount DESC

Edited by - Snory on 11/11/2010 14:03:18
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000