How to effectively model a property in a relational database?

database-design

I have seen two approaches to modeling properties in relational databases:

  1. Create arbitrary tables that assume all the necessary components of the property. For example, have tables for room, unit, floor, building.

  2. Have a single table 'asset'. Use a linking table to create relations between assets.

In either case, the building is represented as a general tree structure. This seems painful to query.

I need to represent this tree in order to implement an RBAC system. Now I'm not so sure that a relational DB is the best solution.

Best Answer

It sounds like your question is about how to store tree data in an RDBMS. I can't speak to using a graph DB, but can for a RDBMS.

Overview

Two common approaches for storing tree data in an RDBMS are:

  • Adjacency list (I think this is what you are describing above)
  • Nested set

Here is a good overview of how to implement each. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

And a comparison of performance (in SQL Server, but the basic conclusions should be similar across most databases that allow recursive queries): https://explainextended.com/2009/09/25/adjacency-list-vs-nested-sets-sql-server/

In general, if your tree data isn't going to change and all you need to is to grab all descendants, a nested set is a great solution. If you are frequently updating the tree (adding/removing nodes) then a nested set is not a great solution.

A possible solution

One solution I have implemented in the past is to store distinct trees (it sounds like "Property" would be a distinct tree in your case) as an Adjacency List table in the DB, due to ease of inserts/deletions. Then read a distinct tree into your preferred data structure server-side for operations. I don't know if that works in your case.

So something like

[Asset] table
- id (pk)
- Parent_AssetID (fk referencing Asset.id)
- Name
- Type
- PropertyID (fk referencing Property.id)

[Property] table
- id
- Name

Then you can use the PropertyID column - foreign key to a Property table - to easily query the Asset table and grab all "assets" for that property for processing server-side.