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