In SQL Server 2008 Microsoft added the hierarchyid data type, one of those things that I had in mind but never got to use, till recently...
So I sat down just to play & getting to know how to use it.
Started with my favorite subject: Music Library.
So I Added a sample DB with Artist, Album & Song tables.
MSDN tutorials contained samples where the hierarchy was used in the same table, that's great for an hierarchy where the hierarchy is within the same entity like employees sample (manager is an employee who manages other employees...).
I wanted an hierarchy between different types of entities, so the hierarchy will be handled in the "MusicLibrary" table and the Artist, Album & Song tables will point to it.
The GlobalEntityType table will contain a lookup table for all the entity types:
CREATE TABLE [dbo].[GlobalEntityType](
[Code] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_GlobalEntityType] PRIMARY KEY CLUSTERED
(
[Code] ASC
)
) ON [PRIMARY]
GO
INSERT INTO GlobalEntityType (Name) VALUES ('Artist')
INSERT INTO GlobalEntityType (Name) VALUES ('Album')
INSERT INTO GlobalEntityType (Name) VALUES ('Song')
GO
The MusicLibrary will contain the hierarchy:
CREATE TABLE [dbo].[MusicLibrary]( [MusicLibraryId] [int] IDENTITY(1,1) NOT NULL, [LibraryHierarchyId] [hierarchyid] NULL, [LibraryHierarchyLevel] AS ([LibraryHierarchyId].[GetLevel]()) PERSISTED, [EntityTypeCode] [smallint] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[MusicLibrary] WITH CHECK ADD CONSTRAINT [FK_MusicLibrary_GlobalEntityType] FOREIGN KEY([EntityTypeCode]) REFERENCES [dbo].[GlobalEntityType] ([Code]) GO ALTER TABLE [dbo].[MusicLibrary] CHECK CONSTRAINT [FK_MusicLibrary_GlobalEntityType] GO
The Artist table:
CREATE TABLE [dbo].[Artist]( [ArtistId] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [MusicLibraryId] [int] NULL, CONSTRAINT [PK_Artist] PRIMARY KEY CLUSTERED ( [ArtistId] ASC ) ) ON [PRIMARY] GO ALTER TABLE [dbo].[Artist] WITH CHECK ADD CONSTRAINT [FK_Artist_MusicLibrary] FOREIGN KEY([MusicLibraryId]) REFERENCES [dbo].[MusicLibrary] ([MusicLibraryId]) GO ALTER TABLE [dbo].[Artist] CHECK CONSTRAINT [FK_Artist_MusicLibrary] GO
The Album table:
CREATE TABLE [dbo].[Album]( [AlbumId] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [MusicLibraryId] [int] NULL, CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED ( [AlbumId] ASC ) ) ON [PRIMARY] GO ALTER TABLE [dbo].[Album] WITH CHECK ADD CONSTRAINT [FK_Album_MusicLibrary] FOREIGN KEY([MusicLibraryId]) REFERENCES [dbo].[MusicLibrary] ([MusicLibraryId]) GO ALTER TABLE [dbo].[Album] CHECK CONSTRAINT [FK_Album_MusicLibrary] GO
The Song table:
CREATE TABLE [dbo].[Song]( [SongId] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [MusicLibraryId] [int] NULL, CONSTRAINT [PK_Song] PRIMARY KEY CLUSTERED ( [SongId] ASC ) ) ON [PRIMARY] GO ALTER TABLE [dbo].[Song] WITH CHECK ADD CONSTRAINT [FK_Song_MusicLibrary] FOREIGN KEY([MusicLibraryId]) REFERENCES [dbo].[MusicLibrary] ([MusicLibraryId]) GO ALTER TABLE [dbo].[Song] CHECK CONSTRAINT [FK_Song_MusicLibrary] GO
Fill it with some data...and finally start using the hierarchyid to build the hierarchy.
We'll start by adding a root node:
INSERT INTO [dbo].[MusicLibrary]
([LibraryHierarchyId])
VALUES
(hierarchyid::GetRoot())
GO
Now we'll build an helper stored procedure to add an artist, album & song to the library.
Use the hierarchyid methods (GetRoot, GetAncestor & GetDescendant in this case) to get the last child of the root and add the new artist under that id.
CREATE PROCEDURE [dbo].[spAddArtist]
@ArtistId AS INT
AS
IF (NOT EXISTS(SELECT ArtistID FROM Artist WHERE ArtistId=@ArtistId AND MusicLibraryId IS NOT NULL))
BEGIN
DECLARE @ARTIST_TYPE smallint = 1
DECLARE @root AS HIERARCHYID, @hid AS HIERARCHYID,
@last_child_hid AS HIERARCHYID;
SET @root = HIERARCHYID::GetRoot();
SET @last_child_hid =
(SELECT MAX(LibraryHierarchyId) FROM dbo.MusicLibrary
WHERE LibraryHierarchyId.GetAncestor(1) = @root);
SET @hid = @root.GetDescendant(@last_child_hid, NULL);
DECLARE @MusicLibraryId int
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO dbo.MusicLibrary(LibraryHierarchyId,EntityTypeCode)
VALUES(@hid, @ARTIST_TYPE)
SET @MusicLibraryId = SCOPE_IDENTITY()
UPDATE Artist
SET MusicLibraryId = @MusicLibraryId
WHERE ArtistId = @ArtistId
COMMIT TRAN
END
Similar logic when adding album & song to the library, but this time we'll send the 'parent' artist/album id to the stored procedure:
CREATE PROCEDURE [dbo].[spAddAlbum]
@AlbumId AS INT, @ArtistId AS INT
AS
DECLARE @albumHierarchyId AS HIERARCHYID, @artistHierarchyId AS HIERARCHYID,
@last_child_hid AS HIERARCHYID;
DECLARE @ARTIST_TYPE smallint = 1
DECLARE @ALBUM_TYPE smallint = 2
IF (NOT EXISTS(SELECT AlbumId FROM Album WHERE AlbumId=@AlbumId AND MusicLibraryId IS NOT NULL))
BEGIN
SET @artistHierarchyId = (SELECT LibraryHierarchyId
FROM dbo.MusicLibrary
INNER JOIN Artist ON
Artist.MusicLibraryId = MusicLibrary.MusicLibraryId
AND MusicLibrary.EntityTypeCode = @ARTIST_TYPE
WHERE Artist.ArtistId = @ArtistId);
SET @last_child_hid =
(SELECT MAX(LibraryHierarchyId) FROM dbo.MusicLibrary
WHERE LibraryHierarchyId.GetAncestor(1) = @artistHierarchyId);
SET @albumHierarchyId = @artistHierarchyId.GetDescendant(@last_child_hid, NULL);
DECLARE @MusicLibraryId int
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO dbo.MusicLibrary(LibraryHierarchyId,EntityTypeCode)
VALUES(@albumHierarchyId, @ALBUM_TYPE)
SET @MusicLibraryId = SCOPE_IDENTITY()
UPDATE Album
SET MusicLibraryId = @MusicLibraryId
WHERE AlbumId = @AlbumId
COMMIT TRAN
END
Now use these stored procedures to connect all the entities together:
exec spAddArtist 1 exec spAddArtist 2 exec spAddAlbum 1,1 exec spAddAlbum 2,1 exec spAddAlbum 3,2 exec spAddAlbum 4,2 exec spAddSong 1,3 exec spAddSong 2,3 exec spAddSong 3,4 exec spAddSong 4,4 exec spAddSong 5,4 exec spAddSong 6,1 exec spAddSong 7,1 exec spAddSong 8,1 exec spAddSong 9,2 exec spAddSong 10,2 exec spAddSong 11,2
And final step, lets see how we can query the hierarchy
DECLARE @ARTIST_TYPE smallint = 1, @ALBUM_TYPE smallint = 2, @SONG_TYPE smallint = 3
DECLARE @SampleArtist hierarchyid
SELECT @SampleArtist = MusicLibrary.LibraryHierarchyId
FROM Artist
INNER JOIN MusicLibrary ON
Artist.MusicLibraryId = MusicLibrary.MusicLibraryId
AND EntityTypeCode = @ARTIST_TYPE
WHERE ArtistId = 2
-- Albums of specific artist
SELECT Album.*
FROM Album
INNER JOIN MusicLibrary ON
Album.MusicLibraryId = MusicLibrary.MusicLibraryId
AND EntityTypeCode = @ALBUM_TYPE
WHERE LibraryHierarchyId.IsDescendantOf(@SampleArtist) = 1
--Songs of specific artist
SELECT Song.*
FROM Song
INNER JOIN MusicLibrary ON
Song.MusicLibraryId = MusicLibrary.MusicLibraryId
AND EntityTypeCode = @SONG_TYPE
WHERE LibraryHierarchyId.IsDescendantOf(@SampleArtist) = 1
So you may ask: "what do I need this complexity? we can add a reference from song to album, from album to artist and that's it..."
Well...it's not a better solution, just a different one, I can think of two advantages:
1. What if we add a 'single', a 'single' is a song that is a child of an artist not an album.
If we use foreign keys we'll add another key to song pointing the artist table and than the song table would have sometimes an albumId with a value and artistId with null or vice versa - doable but not so elegant.
With hierarchy we can add a song under an artist without adding another key, just relate the song under the required artist...
2. Isn't this elegant :
SELECT CASE
WHEN Artist.Name IS NOT NULL THEN
Artist.Name
WHEN Album.Name IS NOT NULL THEN
REPLICATE(' ', LibraryHierarchyLevel) + Album.Name
WHEN Song.Name IS NOT NULL THEN
REPLICATE(' ', LibraryHierarchyLevel) + Song.Name
END AS Name
FROM MusicLibrary
LEFT JOIN Artist ON Artist.MusicLibraryId = MusicLibrary.MusicLibraryId
AND EntityTypeCode = @ARTIST_TYPE
LEFT JOIN Album ON Album.MusicLibraryId = MusicLibrary.MusicLibraryId
AND EntityTypeCode = @ALBUM_TYPE
LEFT JOIN Song ON Song.MusicLibraryId = MusicLibrary.MusicLibraryId
AND EntityTypeCode = @SONG_TYPE
WHERE EntityTypeCode IS NOT NULL
ORDER BY LibraryHierarchyId
Till next time..
Diego
