SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 hierarchical linking of locations to each other
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

83 Posts

Posted - 11/01/2013 :  09:44:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5919 Posts

Posted - 11/01/2013 :  10:18:36  Show Profile  Reply with Quote
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

Edited by - TG on 11/01/2013 10:25:49
Go to Top of Page

stahorse
Yak Posting Veteran

83 Posts

Posted - 11/04/2013 :  03:34:59  Show Profile  Reply with Quote
actually LocationID is my primary key....

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

Edited by - stahorse on 11/04/2013 03:48:19
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/04/2013 :  04:02:56  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

83 Posts

Posted - 11/04/2013 :  04:31:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/04/2013 :  06:35:08  Show Profile  Reply with Quote
insert a record for Gauteng with ParentLocationID same as LOcationID for Johannesburg

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000