hierarchical linking of locations to each other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

hierarchical linking of locations to each other

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-11-01 : 09:44:42

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-01 : 10:18:36

one way to model it is this. The PK will ensure that a location can only have one parent.
You can use a recursive common table expression to select out the hierarchy


create table locationHierarchy
       (locationID int not null
       ,parentLocationID int null) --NULL will be the roots
go
alter table locationHierarchy
add constraint PK_locationHierarchy primary key (locationID)
go

EDIT:
if you add a primary key to your location table then you can add your referential integrity:


go
alter table locationHierarchy
add constraint FK_locationid_LocationID foreign key (locationid) references dbo.location(locationid)
go
alter table locationHierarchy
add constraint FK_ParentLocationid_LocationID foreign key (parentLocationid) references location(locationid)
go

Be One with the Optimizer
TG

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-11-04 : 03:34:59

actually LocationID is my primary key....

So are you saying I create another table, locationHierarchy, then link it to my location?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 04:02:56

why not add simply a column ParentLocationID to your current table with datatype same as LocationID field and of NULLable type. Then create a FK on it to link back to Table.LocationID.
SO root level will have ParentLocationID as NULL and all other levels will have immediate parent's LocationID as value for ParentLocationID

see similar setup here for EMployee table

http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-11-04 : 04:31:04

quote:
Originally posted by visakh16

why not add simply a column ParentLocationID to your current table with datatype same as LocationID field and of NULLable type. Then create a FK on it to link back to Table.LocationID.
SO root level will have ParentLocationID as NULL and all other levels will have immediate parent's LocationID as value for ParentLocationID

see similar setup here for EMployee table

http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I get that example, 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 06:35:08

insert a record for Gauteng with ParentLocationID same as LOcationID for Johannesburg

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources