I would like to use the & rdquo; model & ldquo; nested set model & rdquo; but I am obliged to have a GUID as the main key. How can I do without integers as a pk?

advertisements

I'm not aware how deep my tree will be. So I think the NSM is fit for me, reading some docs. In sql, this model suppose I'm using an integer value as primary key. I thought to create a twin table only to store the ints (PK,left,righ) connected by a relation one-to-one with the real table. Things are complicating and it is a waste of space disk, especially when the server is not mine and I have to pay each megabyte. Help!!

UPDATE

Excellent! Fabolous!! Thanks Macka and Bill, I could skip reading a whole book, for now. Celko is a future order on Amazon. ;-)


It doesn't matter what type your primary key is as the left/right values will still be integers. eg.

CREATE TABLE [dbo].[Demo](
    [ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Demo_ID]  DEFAULT (newid()),
    [Name] [varchar](50) NOT NULL,
    [Lft] [int] NOT NULL,
    [Rgt] [int] NOT NULL,
 CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--add some test data

INSERT INTO demo(name,lft,rgt)
SELECT 'node1',1,6
UNION
SELECT 'node2a',2,3
UNION
SELECT 'node2b',4,5

--check it works

SELECT *
FROM demo
WHERE lft>=2
ORDER BY lft