Use hierarchyid to store address of a customer

Question

I have a table named 'AddressDemo' to store address of a customer with the following fields,

CREATE TABLE [dbo].[AddressDemo](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[State] [nvarchar](50) NULL,
[District] [nvarchar](50) NULL,
[Taluk] [nvarchar](50) NULL,
[Village] [nvarchar](50) NULL,
[Street1] [nvarchar](50) NULL,
[Street2] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
 CONSTRAINT [PK_AddressDemo] PRIMARY KEY CLUSTERED 
(
    [AddressID] ASC
))

Where there is a hierarchy exists, which is akin to State --> District --> Taluk --> Village --> Street1 --> Street2

Isn't it a good idea to keep a separate table to store the hierarchy so that we can avoid duplication of data. How is the following

CREATE TABLE [dbo].[LocationDemo](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[LocationNodeID] [hierarchyid] NULL,
[Location] [nvarchar](50) NULL,
 CONSTRAINT [PK_LocationDemo] PRIMARY KEY CLUSTERED 
(
    [LocationID] ASC
))

So the 'AddressDemo' will look like the following

CREATE TABLE [dbo].[AddressDemo](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NULL,
[Phone] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
 CONSTRAINT [PK_AddressDemo] PRIMARY KEY CLUSTERED 
(
    [AddressID] ASC
))

and LocationID of AddressDemo reference to LocationID of LocationDemo.

score 1 · Accepted Answer · answered Aug 28 '11 at 01:34

While your proposed solution is more dynamic than the flattened solution you described I would not go with a completely dynamic schema for locations in this case. Adding hierarchical processing is not something to be done without good reason because it complicates your database queries later on and limits your performance optimisation alternatives (views containing CTEs cannot be indexed, and you would need views to reasonably consume this data by your application).

If you're talking about a low volume system or one in which the number of addresses being stored is small you can play with the dynamic address element route, but considering the fact that no one address would logically exist without the majority of the location elements I would again say it's overkill.

Go for a more normalized route without going overboard. Consider making a State table and a FK to that table from Address, a District table and a FK and so on...

I would like to avoid creating multiple tables(that is a old method) — Rauf, Aug 28 '11 at 02:36
Normalizing a relational database schema to the appropriate degree never goes out of style — Tahbaza, Aug 28 '11 at 20:36

Use hierarchyid to store address of a customer

1 Answers1