Is there a cleaner way to do this

[Geekitygeek]

Hi guys, I'm trying to model inheritence in a SQL database. I have a working structure but I'm wondering if theres a neater way of doing this.

The database is to store product information. A product is made up of multiple parts and each part is made up of components. Some parts are generic and are used by many products, others are more specific configurations of the same product. Coming from a C#.net background this instantly looked like an inheritance model since the specific configurations will only change one or two components and share the rest with the base generic part.

I have a little experience with SQL and SQL Server and I have put together the following tables (see attached image) to model this behaviour along with a query which finds all the components or a part. The query pulls out all the components of the part and any of the parts it inherits from then exludes parts that have been replaced.

I was hoping someone with a stronger knowledge of SQL could cast an eye over it and let me know if I'm reinventing the wheel here:

DECLARE @PartID int = 5;

--find all Parent parts
WITH IDS(ParentID, Level) AS
(
	SELECT p.PartID, 0
	FROM Parts p LEFT JOIN SubParts s ON s.ChildID = p.PartID
	WHERE p.PartID = @PartID
	UNION ALL
	
	SELECT s.ParentID, i.Level + 1
	FROM SubParts s
	INNER JOIN IDS i ON i.ParentID = s.ChildID
)

--find all components in part and its parents
SELECT pc.Quantity, c.Name
FROM Parts p INNER JOIN PartsComponents pc ON p.PartID = pc.PartID
INNER JOIN Components c ON pc.ComponentID = c.ComponentID  LEFT JOIN PartReplacements pr ON p.PartID = pr.PartID AND pc.ComponentID = pr.ComponentID
WHERE p.PartID IN (SELECT ParentID FROM IDS) AND CAST(p.PartID as nvarchar) + '|' + CAST(pc.ComponentID as nvarchar) NOT IN
(
--exlude parts that have been replaced
SELECT CAST(pr.ReplacesPartID as nvarchar) + '|' + CAST(pr.RepalcesComponentID as nvarchar)
FROM Parts p INNER JOIN PartsComponents pc ON p.PartID = pc.PartID
INNER JOIN Components c ON pc.ComponentID = c.ComponentID  LEFT JOIN PartReplacements pr ON p.PartID = pr.PartID AND pc.ComponentID = pr.ComponentID
WHERE p.PartID IN (SELECT ParentID FROM IDS) AND CAST(pr.ReplacesPartID as nvarchar) + '|' + CAST(pr.RepalcesComponentID as nvarchar) is not null
)