DynamoDB Schema - Modeling Locations inside a building

Question

New to DynamoDB over here. I need to figure out a DynamoDB schema for different locations inside a building. Additionally, I need to be able to identify computers assigned to each of the locations. The locations are nested inside other locations. For instance,

Building 1
- Wing A
  - Floor 1
    - Section A
      - Office 1
        
        Computer A
        
        Computer B
      - Office 2
        
        Computer A
    - Section B
      - Office 1

... and so on.

ACCESS PATTERNS:

Show all of the locations (wings, Floors, sections, etc) in a building
Show a specific location
Show all of the computers assigned to a specific location
Show the location of a specific computer

What I was thinking:

I initially wanted to create something like this:

PartitionKey              SortKey                                 Attributes

Building#1                Building#1 (For metadata)
Building#1                Section#1                                 [...]
Building#1                Section#1|Section#2                       [...]
Building#1                Section#1|Section#2|Section#3             [...]

I know this is the wrong way to think about it, but I can't figure out any other way.

What is the best way to model the location of sections, offices, etc of a building?

score 1 · Accepted Answer · answered Jan 24 '20 at 23:06

If those are really the only access patterns you can probably do something with a simple GSI. I wouldn't using Building as the PartitionKey because this will give you a lot of hot spots in the data. Something like this would probably work:

PartitionKey        SortKey     GSI_PartitionKey GSI_SortKey            Attributes
Building#1          'Location'                                          [...]
Wing#1              'Location'  'Location'       Building#1 .           [...]
Floor#1             'Location'  'Location'       Building#1|Wing#A      [...]
.
.
.
Computer#1          'Computer'  'Computer'       B#1|W#A|F#1|S#A|O#1    [...]
Computer#2          'Computer'  'Computer'       B#1|W#A|F#1|S#B|O#1    [...]
.
.
.

The SortKey values here are more optional, but they tend to allow for changes later without as much work now.

To get all the locations in a building you query the GSI where the GSI_PartitionKey is 'Location' and the GSI_SortKey begins with your building ID. You can add sub locations to the string so you can get all the locations in Wing A with a begins with of Building#1|Wing#A|

Get a specific location using the PartitionKey (and optionally the SortKey = 'Location').

To get all the computers in a locations GSI where the GSI_PartitionKey is 'Computer' and the GSI_SortKey begins with your location ID.

Get a specific computer using the PartitionKey (and optionally the SortKey = 'Computer') the attributes should include it's location.

Thanks a lot Jason ... I know you mentioned that the sort key is optional, but how could it be used in the future? I have been thinking and I can't seem to find a way to use it. — Rookie, Jan 26 '20 at 12:36
Say you wanted to store some other information about a location, that you didn’t want to put in the main record. Maybe because that would make the record too large, or maybe because it’s related to it, but not part of it. You may still want to look it up by the location’s ID. To do this you need something to differentiate it from the actual location record. That’s where the sort key comes in handy. The new record would have some other value in the sort key, so you can still look it up by ID, but store two different types of data. — Jason Wadsworth, Jan 26 '20 at 17:15

score 0 · Answer 2 · answered Jan 24 '20 at 22:59

I think you're on the right track...
Having the hierarchical data coded as a delimited sort key seems to follow the recommendations I've seen (though your two sets of example data don't match) Section#1|Section#2|Section#3 vs Wing A|Floor 1|Section A

I'd probably consider having the table with just a hash of "serial number" or "asset ID"

Then have a GSI with the key's you describe.

DynamoDB Schema - Modeling Locations inside a building

2 Answers2