surreal_warehousing

This repository explores how a simple warehousing system could be implemented using SurrealDB.

Setup

Our warehouse will be represented in a tree like structure. There are containers which themselves can contain containers. The leave nodes of our tree are made up of items whose location we ultimately want to track in our warehousing system.

There will be a relation table contained_in hierachically linking containers. The table will have three nullable fields x_position, y_position, z_position.

This hierarchical structure can be arbitrarily deep.

In a relational database management system we could represent the same situation with a container table that has a foreign-key relation with itself. Each container could have a reference to its parent container. Querying such a database could need many joins. One per level in the tree we want to inspect in our query. We will explore if a graph-like data model in SurrealDB makes working with such a hierarchical data model easier (and potentially more performant) for us.

This is how the container table is defined:

define table if not exists container schemafull;

define field code on table container type string;
define field has_x on table container type bool default false;
define field has_y on table container type bool default false;
define field has_z on table container type bool default false;

define field x_min on table container type option<int> default None;
define field x_max on table container type option<int> default None;

define field y_min on table container type option<int> default None;
define field y_max on table container type option<int> default None;

define field z_min on table container type option<int> default None;
define field z_max on table container type option<int> default None;

//additional fields here would make sense in a more complete implementation like 
//names for the dimensions like "shelve", "rack", "slot" etc.

The contained_in relation table looks like this:

define table contained_in type relation in container|item out container schemafull;

define field x_pos on table contained_in type option<int>;
define field y_pos on table contained_in type option<int>;
define field z_pos on table contained_in type option<int>;

And the item table is trivial since it only has a code field for the name of each item:

define table if not exists item schemafull;
define field if not exists code on table item type string;

Querying the test database

Get all child containers for a given container.

select in, x_pos, y_pos, z_pos
from contained_in
where out = container:box1;

Returns an array containing only our single test item without any position information.

[
    {
        in: item:item1,
        x_pos: NONE,
        y_pos: NONE,
        z_pos: NONE
    }
]

The recursive paths in SurrealQL allows the user to recursively traverse the warehouse tree up to a certain number of levels.

item:item1.{2}(->contained_in->container).code;

"Get item1s parent parent container code". Returns

[
    'slot1'
]

We can also traverse "up to four levels" in our warehouse container tree like this:

item:item1.{1..4}.{code, contained_in: ->contained_in->(?).@};

Which returns:

{
    code: 'item1',
    contained_in: [
        {
            code: 'box1',
            contained_in: [
                {
                    code: 'slot1',
                    contained_in: [
                        {
                            code: 'rack1',
                            contained_in: []
                        }
                    ]
                }
            ]
        }
    ]
}

This shows us the nesting of containers very nicely from item1 to rack1. rack1 being the root of the container tree in our example. That's why only three levels are returned.

The graph like data modelling and querying that is possible with SurrealDB seems like a nice fit for a domain like our warehouse and lends itself to this approach. The SurrealQL syntax needs a little getting used to but it is extensively documented.

The question of performance here between a relational data model where we would have a container table with a self-referential foreign-key relationship with the table itself versus the one explored here is beyond the scope of this little experiment unfortunately.

Table Constraints

In our warehouse system it seems reasonable to introduce further constraints on the contained_in records. Containers can have size limits (but don't have to) and positions of containers or items inside other containers can be recorded but aren't mandatory. We might want to be able to represent an unordered bag of items or a well structured shelve.

The values for x_pos, y_pos and z_pos in the contained_in table can be None (or Null which is apparently the same type in SurrealDB? 👀) but don't have to. This means that a index on these three fields with unique constraint won't cut it since in a bag of items all of them would have the same None values for x_pos, y_pos and z_pos.

In summary we need to ensure that item or container positions are within the limits of the parent container and that no two containers can be documented as occupying the same position in their parent container. Oh and by the way... one item or container can not be in two places at the same time.

The constraint that each item or container be in one place at a time can be asserted by creating an index on the contained_in table.

define index idx_one_contained_in_only on table contained_in columns in unique;

DEFINE FIELD with assert statements

The logic checking whether a contained_in record actually is valid and can be created needs to take into account the values of multiple fields. The position values (x_pos, y_pos, z_pos) and the respective ...min and ...max dimensions of the related parent container.

Thus adding ASSERT clauses to individual fields might not be the best way to implement the necessary logic.

DEFINE EVENT to the rescue?

Let's explore whether we can implement our constraints on contained_in entries by using events. As it turns out via EVENTS this constraining logic can be implemented.

define event contained_in_created on table contained_in
    when $event == "CREATE"
    then {
        if $after.out.has_x and $after.x_pos == None {
            THROW "Can't create contained_in relation: x position is required";
        } else if $after.out.has_y and $after.y_pos == None {
            THROW "Can't create contained_in relation: y position is required";
        } else if $after.out.has_z and $after.z_pos == None {
            THROW "Can't create contained_in relation: z position is required";
        } else if $after.out.has_x and !($after.out.x_min <= $after.x_pos and $after.x_pos <= $after.out.x_max) {
            THROW "Can't create contained_in relation: x position is out of bounds";
        } else if $after.out.has_y and !($after.out.y_min <= $after.y_pos and $after.y_pos <= $after.out.y_max) {
            THROW "Can't create contained_in relation: y position is out of bounds";
        } else if $after.out.has_z and !($after.out.z_min <= $after.z_pos and $after.z_pos <= $after.out.z_max) {
            THROW "Can't create contained_in relation: z position is out of bounds";
        } else if array::len(select *
                from contained_in
                where out == $after.out
                    and x_pos==$after.x_pos
                    and y_pos==$after.y_pos
                    and z_pos==$after.z_pos
                ) > 1
        {
            THROW "Can't create contained_in relation: position in container is already occupied";
        }
    };