Celko's Netsed Sets vs Adjacency List Models

I am currently modelling a db to store geneaology information (family tree)
Each node on the tree will always have zero, one or two parent nodes,
depending upon whether the details of both parents are known.
I have been initially modelling this using a simple adjacency list model
however I would appreciate any feedback on the advantages and
implementation of a nested set model for this application.
CREATE TABLE persons (
PersonID INT,
MotherID INT,
FatherID INT,
Surname VARCHAR(50),
Firstname VARCHAR(25)
)
In this scenario both the MotherID & FatherID would relate back to PersonID.
Thanks
MurphHi
I'd go with below design
CREATE TABLE Parents
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parents VALUES (1,'A')
INSERT INTO Parents VALUES (2,'B')
INSERT INTO Parents VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
ParentId INT NOT NULL FOREIGN KEY REFERENCES Parents([ID])ON DELETE
CASCADE ON UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO F VALUES (1,1,'AA')
INSERT INTO F VALUES (2,1,'AA')
INSERT INTO F VALUES (3,2,'BB')
INSERT INTO F VALUES (4,2,'BB')
INSERT INTO F VALUES (5,2,'BB')
INSERT INTO F VALUES (6,3,'CC')
--OR
CREATE TABLE Persons
(
Child_Id int NOT NULL,
Parentid int NULL,
ChildName varchar(25) NOT NULL,
CONSTRAINT PK_Persons_Childid PRIMARY KEY(Child_Id),
CONSTRAINT FK_Persons_Parentid _Child_Id
FOREIGN KEY(Parentid )
REFERENCES Persons(Child_Id)
)
"Murphy" <m@.urphy.com> wrote in message
news:_jO1e.15253$C7.5562@.news-server.bigpond.net.au...
> I am currently modelling a db to store geneaology information (family
tree)
> Each node on the tree will always have zero, one or two parent nodes,
> depending upon whether the details of both parents are known.
> I have been initially modelling this using a simple adjacency list model
> however I would appreciate any feedback on the advantages and
> implementation of a nested set model for this application.
> CREATE TABLE persons (
> PersonID INT,
> MotherID INT,
> FatherID INT,
> Surname VARCHAR(50),
> Firstname VARCHAR(25)
> )
> In this scenario both the MotherID & FatherID would relate back to
PersonID.
> --
> Thanks
> Murph

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

Casting question

HI all,
Quick question about a trigger i am developing.
I need to take a varchar string variable and convert it and store it in an
integer variable.
How do i write that statement.
Pls keep in mind that this is inside a trigger not inside a SQL statement.
Thanks in advance,
Colin
csmart@.nf.sympatico.caassuming the value is a number value an inplicit conversion will occur
take a look at this
declare @.v varchar(50),@.i int
select @.v ='1212121'
select @.i =@.v -- implicit conversion
select convert(int,@.v),@.i
the only problem you will have is if the value is bigger than an int
can hold or not a number
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||try isnumeric function to check if its a valid numeric