I am trying to create a table to manage parent/child relationships for a website.  This is not too difficult if it were for one site, however I would like to use this table for multiple sites, as well as subsites.

For example, I have a website ALPHA, a website BETA as well as a Website GAMMA, but the root of website GAMMA is also a page in website ALPHA.

A
|-B
|-|-C
|-|-D
|-|-|-E
|-|-F
|-G
|-H

M
|-N
|-O
|-P
|-|-Q
|-|-R

B
|-C
|-D
|-|-E
|-F

As you can see above, site GAMMA’s root of B is also a child of site ALPHA’s A.

This is my schema

IF OBJECT_ID (N'Sites_GetTreeFromPage') IS NOT NULL
	DROP FUNCTION Sites_GetTreeFromPage
GO

CREATE FUNCTION dbo.Sites_GetTreeFromPage (
	@Page_id uniqueidentifier
	, @Site_id uniqueidentifier = NULL
	, @StartDepth int = 0
	, @Depth int = NULL
) RETURNS @PageTree TABLE (
	Page_id uniqueidentifier PRIMARY KEY NOT NULL
	, L int NOT NULL
	, R int NOT NULL
	, D int NOT NULL
	, Site_id uniqueidentifier NOT NULL
) AS
BEGIN
	SELECT TOP 1 @Site_id = ISNULL(@Site_id, Site_id)
	FROM Sites_Pages
	WHERE Page_id = @Page_id
	ORDER BY D ASC

	INSERT INTO @PageTree (Page_id, L, R, D, Site_id)
	SELECT
		p.Page_id
		, tree.L
		, tree.R
		, tree.D
		, s.Site_id
	FROM
		Sites_Pages s_p
		JOIN Sites_Pages tree ON tree.Site_id = s_p.Site_id AND tree.L >= s_p.L AND tree.R <= s_p.R
		JOIN Pages p ON tree.Page_id = p.Page_id
		JOIN Sites s ON s_p.Site_id = s.Site_id
	WHERE
		s_p.Page_id = @Page_id
		AND s_p.Site_id = @Site_id
		AND tree.D >= s_p.D+@startDepth
		AND (@Depth IS NULL OR tree.D <= s_p.D+@Depth+@startDepth)

	RETURN
END
GO

IF OBJECT_ID (N'Sites_GetPageTree') IS NOT NULL
	DROP PROCEDURE Sites_GetPageTree
GO

CREATE PROCEDURE Sites_GetPageTree
	@Page_id uniqueidentifier
	, @Site_id uniqueidentifier = NULL
	, @StartDepth int = 0
	, @Depth int = NULL
AS
BEGIN
	SELECT
		p.Page_id
		, p.Title
		, REPLICATE('| - ', tree.D-@startDepth) + p.Title AS Tree
		, tree.L
		, tree.R
		, tree.D
		, s.Title AS Site
		, CASE WHEN tree.L = 1 THEN 1 ELSE 0 END AS IsRoot
		, CASE WHEN tree.R = tree.L + 1 THEN 1 ELSE 0 END AS IsLeaf
		, CASE WHEN tree.R > tree.L + 1 THEN 1 ELSE 0 END AS IsParent
	FROM
		dbo.Sites_GetTreeFromPage(@Page_id, @Site_id, @StartDepth, @Depth) tree
		JOIN Pages p ON tree.Page_id = p.Page_id
		JOIN Sites s ON tree.Site_id = s.Site_id
	ORDER BY
		tree.L
		, tree.R
END
GO

IF OBJECT_ID (N'Sites_GetPageChildren') IS NOT NULL
	DROP PROCEDURE Sites_GetPageChildren
GO

CREATE PROCEDURE Sites_GetPageChildren
	@Page_id uniqueidentifier
AS
BEGIN
	EXEC Sites_GetPageTree @Page_id = @Page_id, @StartDepth = 1, @Depth = 0
END
GO

IF OBJECT_ID (N'Sites_GetSiteTree') IS NOT NULL
	DROP PROCEDURE Sites_GetSiteTree
GO

CREATE PROCEDURE Sites_GetSiteTree
	@Site_id uniqueidentifier
AS
BEGIN
	DECLARE @Page_id uniqueidentifier
	SELECT
		@Page_id = RootPage_id
	FROM
		Sites
	WHERE
		Site_id = @Site_id

	EXEC Sites_GetPageTree @Page_id = @Page_id, @Site_id = @Site_id
END
GO

I am trying to write stored procedures to interact with the Sites_Pages table, but it has been hurting my brain.

The complication is that I would like to perform all operations without having to run different queries for different Sites. Also, I don’t want to use a temporary table to store rows if I want to move Pages from one parent to another.