Hierarchy vs many tables

Question

We have a requirement to store locations. There are different types of locations. Areas, Blocks, Buildings, Floors, Rooms and Beds. So, a Bed is in a room, which is on a floor etc.

I think I have two options. First is to have a table for each type. And a foreign key to keep them all linked.

OR...

CREATE TABLE [dbo].[Location]
(
 [ID] Int IDENTITY(1,1) NOT NULL,
 [ParentID] Int NULL,
 [LocationTypeID] Int NOT NULL,
 [Description] Varchar(100) COLLATE Latin1_General_CI_AS NOT NULL
)

A table to hold all locations, in a hierarchical style.

I like the idea of this, as if we add new types, it's data driven. No new table. But, the querying can be expensive I think.

If I want to show bed details (Bed 1 in Room 5 on the 4th floor of the science building...), it's a recursive function, which is more tricky than a simple INNER JOIN of all the tables to get details about a location.

One thing though.

I need to record movements. And a movement might be from a room, to an area. So, with separate tables, it will be hard to record movements in a single 'movement' table, as which table do I FK to? With a hierarchy, it's very easy.

Also, reporting on where 1000 people are, would call the recursive query a lot to produce results. Slow? Or is there a clean way to get around this?

Answer 1

All methods have their own pros and cons. Another approach I have seen (especially with dates) is to use one table and encode the hierarchy in a single field (no parent id).

For example ID (integer column) = 1289674566 would mean bed 66, floor 45, etc...

It will require a little work when you need to "extract" a specific hierarchy level (for example to count the number of distinct buildings) but arithmetic operations are quite fast and you can build views on top of the base table if you want to make life easier for end users.

Just another option...

Answer 2

My canned suggestion is to store the data how it is in the real world, then if you're lucky you can query it without too much of a hit. If there is too much of a hit, extract the data into a format that you can easily search.

In your case, I would go with the hierarchical style you are thinking. That way you can have a building with a room with a dresser with a drawer with a box in a box. You can then move the dresser to another room and all the stuff goes with it.

You'll find the recursive CTE's are fast as long as you're not trying to 'trick' SQL server into doing something.

I just answered a hierarchical question here that has a good example for you to play with. In particular pay attention to the SORT_PATH. In the example I build a SORT_PATH that looks something like this:

TEST01.TEST03.LABSTL

SSRS: Recursive Parent Child

You can store this value in your table on EDIT/UPDATE and it can do a lot for you (for performance) as long as you don't mind the hit when you're updating the record.

If you do mind the hit on an update you can use a backend process to keep your SORT_PATH updated. In the past I've used a "DIRTY BIT" field that gets flipped when something is modified; a backend process then comes through and updates everything related to that record, but since it's a backend process users don't notice the impact. This is a good job for the SEVICE BROKER to manage -- on edit/update/delete set the DIRTY_BIT=True and send the SERVICE BROKER a message that will kick off a process that updates anything with DIRTY_BIT=True.

Answer 3

Have a look at using the hierachyid data type, it's a CLR datatype native to SQL server and has built in functions for querying parent/child type relationships:

Tutorial: Using the hierarchyid Data Type