hierarchical linking of locations to each other

Question

Post reply

hierarchical linking of locations to each other

hoseam

SSCertifiable

Points: 5533
More actions
November 4, 2013 at 1:36 am

#300729

I need help here, I have this table, I may need to create another column I'm not sure yet.
CREATE TABLE [dbo].[Location](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[Location] [nvarchar](255) NOT NULL,
[Product_Code] [nvarchar](255) NOT NULL,
[LocationProductStockAmt] [decimal](18, 2) NULL)
And I need to create a database object that can store hierarchical linking of locations to each other. Ensure referential integrity is enforced.
One location cannot be linked to more than one parent location.
For example if I have location South Africa and I want to link Gauteng and to Gauteng I want to link Johannesburg. South Africa => Gauteng => Johannesburg

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Sounds like HierarchyID is exactly what you are after

http://technet.microsoft.com/en-us/library/bb677290.aspx

Clear Sky SQL
My Blog[/url]

hoseam SSCertifiable Points: 5533 More actions · Answer 2

hoseam

SSCertifiable

Points: 5533

November 4, 2013 at 2:24 am

#1663742

I'll look at your link thank you

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Edited, try that again now...

Clear Sky SQL
My Blog[/url]

hoseam SSCertifiable Points: 5533 More actions · Answer 4

Dave Ballantyne (11/4/2013)
Edited, try that again now...

I get that, but for my table, I have primary key LocationID, then Location which has:

Johannesburg

Pretoria

East London

Cape Town

Bloemfontein

Rustenburg

Cape Town

Polokwane

Kimberly

Welkom

Now this is what I need Gauteng => Johannesburg in my hierarchy. how do I achieve that

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Now this is what I need Gauteng => Johannesburg in my hierarchy. how do I achieve that

place a parentID column ... following will be the values in the ParentID, if you look closely it will give you tree view (HirerchyID).

ID Location ParentID

1 South Africa 0

2 Gauteng 1

3 Johannesburg 2

hope it helps

hoseam SSCertifiable Points: 5533 More actions · Answer 6

twin.devil (11/4/2013)
Now this is what I need Gauteng => Johannesburg in my hierarchy. how do I achieve that
place a parentID column ... following will be the values in the ParentID, if you look closely it will give you tree view (HirerchyID).
ID Location ParentID
1 South Africa 0
2 Gauteng 1
3 Johannesburg 2
hope it helps

Yes it does, but I'm thinking about my data, this row for instead:

ID Location Product_Code LocationProductStockAmt

1 Johannesburg 1231 4000

will be:

INSERT INTO [dbo].[Location] VALUES ('Johannesburg','1231', 4000)

now how will I insert 'South Africa' and 'Gauteng' and still maintain my values,

because now we have:

ID Location ParentID LocationProductStockAmt

1 South Africa 0 4000

2 Gauteng 1 4000

3 Johannesburg 2 4000

how do we deal with that.

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 7

How many rows will this table eventually have and how often will it need to take new inserts or updates?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hoseam SSCertifiable Points: 5533 More actions · Answer 8

Jeff Moden (11/4/2013)
How many rows will this table eventually have and how often will it need to take new inserts or updates?

1. As many as it could

2. Not very often

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 9

hoseam (11/4/2013)
Jeff Moden (11/4/2013)
How many rows will this table eventually have and how often will it need to take new inserts or updates?
1. As many as it could
2. Not very often

BWAAA-HAAA!!!! ... OK... in that case, please see the following articles. And, it's easy to maintain and troubleshoot because the Adjacency List is what you use to maintain it. It's fast too. Both methods will convert a million node hierarchy in about 54 seconds on any decent box now-a-days.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

http://www.sqlservercentral.com/articles/T-SQL/94570/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)