Hur bygger man upp ett eget artikelnummersystem?

product_categories
|-- id
|-- name

product_subcategories
|-- id
|-- parent (product_categories(id))
|-- name

colors
|--id
|--name

sizes
|--id
|--name

products
|--id
|--category (product_categories(id))
|--subcategory (product_subcategories(id))
|--color (colors(id))
|--size (sizes(id))
|--name
|--product_number

CREATE TABLE products
	(
		product_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
		parent_product_id int NULL FOREIGN KEY REFERENCES products(product_id),
		is_item bit NOT NULL DEFAULT 0,
		name varchar(255) NOT NULL
	)
GO
CREATE OR ALTER PROCEDURE add_product @name varchar(255), @parent_product_id int = NULL, @is_item bit = 1, @product_id int OUTPUT
AS
BEGIN
SET NOCOUNT ON;

	INSERT INTO products(parent_product_id, is_item, name) VALUES(@parent_product_id, @is_item, @name);
	SET @product_id = SCOPE_IDENTITY();
END
GO
DECLARE @product int = NULL, @category int = NULL, @subcategory int = NULL;
EXEC add_product 'Bikes', NULL, 0, @category OUTPUT;
EXEC add_product 'City-Bikes', @category, 0, @subcategory OUTPUT;
EXEC add_product 'Scott YZ1', @subcategory, 1, @product OUTPUT;
EXEC add_product 'Scott YZ2', @subcategory, 1, @product OUTPUT;
EXEC add_product 'Scott YZ3', @subcategory, 1, @product OUTPUT;
EXEC add_product 'Scott YZ4', @subcategory, 1, @product OUTPUT;
WITH a AS (
SELECT product_id, parent_product_id, is_item, cast(name as varchar(max)) as name FROM products WHERE parent_product_id IS NULL
UNION ALL
SELECT p.product_id, p.parent_product_id, p.is_item, a.name + '/' + p.name FROM products p INNER JOIN a ON a.product_id = p.parent_product_id
)
SELECT * FROM a WHERE is_item = 1