Find similar assets in db by comparing tags

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Find similar assets in db by comparing tags

Author

Topic

Snory
Starting Member

2 Posts

Posted - 2010-11-11 : 14:03:02

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources