Implementing warehousing system using rethinkdb: are there any strictly ACID requirements I need?

Question

Situation

I am trying to implement a warehouse system using a traditional database.

The tables are :

1. products (each row representing 1 sku)
2. warehouse_locations ( each row represents a particular shelf in a particular warehouse)
3. pallets (each row represents a particular pallet)
4. user_defined_categories (each row represents a particular user defined category: e.g. reserved, available, total_physical, etc)
5. products_in_pallets_by_categories (each row will have foreign keys of the pallets, products, and user_defined_categories table. will specify quantity of products in a particular pallet of a particular category.)
6. products_in_warehouse_locations_by_categories (each row will have foreign keys of the warehouse_locations, products table, and user_defined_categories. will specify quantity of products in a particular pallet of a particular category.)

What end users want to see/do

End users will update the system about what products are placed/removed on what pallet.

End users will also want to know any time (preferably in real-time) how many reserved or available products are in the warehouse.

So what's my initial plan?

Wanted to use a traditional RDBMS like PostgresQL and a message queue like RabbitMQ to provide real-time updates. By real-time updates, I mean the end users using either a single page application or mobile phone can observe changes in inventory in real-time.

So what's changed?

I came across rethinkdb FAQ and it said

RethinkDB is not a good choice if you need full ACID support or strong schema enforcement—in this case you are better off using a relational database such as MySQL or PostgreSQL.

Why you even considering rethinkdb?

Because if I can use it and it allows real-time updates, it will help tremendously as we expect the client's sales team placing reservations around the world on our system.

What's the most frequent updates/inserts?

The movement of the products from one place to another. I expect plenty of updates/inserts/deletes to the relation tables. Occasionally, I apologise I do not know how to explain this in the rethinkdb paradigm. I am a traditional RDBMS person.

Is the system built yet?

Not yet. Which is why I want to seek an answer regarding rethinkdb before actually proceeding.

Do you expect to use any transactions?

Well, I am not sure.

I can think of a real world case where a warehouse worker moves products (partially or completely) from one pallet to another pallet.

Another real world case will be where a warehouse worker moves the products from a pallet to a warehouse_location (or vice-versa).

Do I definitely need to use transactions? Again, I am not sure.

Cause I expect the workers to update the system AFTER they have physically finished the moving.

I will provide a screen for them to choose

• move from <some dropdown> to <another dropdown>

So what's the question?

Do I need to have full ACID support or strong schema enforcement for my warehouse system based on my user requirements at the moment? And is it implementable using rethinkdb?

I also expect to implement activity streams once the system is implemented which will show events such as Worker A moved 100 units of product A from warehouse shelf 1A to pallet 25.

Answer 1

When you are dealing with things where information must always be accurate and consistent, ACID matters. From what you say, it sounds like it is important.

It sounds to me like you want to just allow real-time updates and that the key problem is in seeing rabbit-mq as the non-real-time component, correct? Why were you even considering RabbitMQ? (If it is to allow the db to go down for maintenance, maybe implement a backup private caching store in sqlite?)

In general you should assume you need ACID compliance until you have a model where eventual consistency is ok. Additionally real-time accurate reporting rules out eventual consistency.