Skip to content

Database design

Jump to bottom
Will Thomson edited this page Nov 22, 2019 · 1 revision

Note: this page requires updating.

RealmDB

The app keeps all data in local storage on the device, so that it is accessible offline. We use realm as our database, because it

  • Is built and optimised specifically for mobile hardware
  • Has tight integration with react-native so we don't need to play with native code
  • Works on both android and iOS
  • Provides a high level interface, taking away coding complexity, e.g. work with data as instances of classes, adding behaviour to the data by adding functions to the class - realm takes care of binding the class each time you retrieve a piece of data of that type
  • Gives some of the trickier database behaviour for free, e.g. will automatically lazy-load data in a ListView without us writing special code
  • Is being well maintained and regularly updated with new features

We have built an additional layer on top of the realm database, which is kept in a separate repository/npm package, react-native-database. Amongst other things, this provides fine-grained change notifications, which at the time we released v1.0 of mobile, weren't available in realm. See the readme of react-native-database for how to subscribe and use these notifications, and the section below on change notifications for specifics on how we use them in mSupply mobile. Our extension also adds a call to a destructor when a database object is deleted, if that object's class defines one.

Within mobile we have yet another lay on top of that, which provides access to a couple of derived data types. That is, it allows you to query for, e.g., Customer data, which is not actually a data type specified in the schema, but instead a subset of all Name data filtered by isVisible and isCustomer.

Data Types

The data types in mSupply mobile's database schema mostly map directly onto a table in mSupply desktop/server. The exceptions are item_line, which is replaced by ItemBatch, transaction_line, which is replaced by TransactionBatch, and the list_master tables, which have the start flipped around to be MasterList.

The full list of data types:

  • Address
  • Item
  • ItemBatch
  • ItemCategory
  • ItemDepartment
  • ItemStoreJoin
  • MasterList
  • MasterListItem
  • MasterListNameJoin
  • Name
  • NameStoreJoin
  • NumberSequence
  • NumberToReuse
  • Requisition
  • RequisitionItem
  • Setting
  • Stocktake
  • StocktakeBatch
  • StocktakeItem
  • SyncOut
  • Transaction
  • TransactionBatch
  • TransactionCategory
  • TransactionItem
  • User

For the fields in each, see the Schema in /database/schema.js or at the bottom of each file for those with classes defined, e.g. Item.Schema at the bottom of database/DataTypes/Item.js (no point in keeping the details here to go out of date as they are pretty clear in the code)

Working with the Database

Realm is a bit different to other databases, but I think it's easier than most once you've learnt it. Below are the functions you'll tend to use, but react-native-database just replicates the API of realm, so see their documentation for more details

For querying

  • database.objects(dataType) - This is the start of a query, returning all data of that type. Don't worry, it returns a ResultSet rather than the raw data, and the ResultSet loads the data lazily as it is required for display or manipulation, so you won't hog all of the device's memory by running this query.
  • results.filtered(filterString, [parameters]) - Once you have a ResultSet out of a database.objects query, you can filter it down to get just the data you want, e.g. const searchResults = transactions.filtered('serialNumber BEGINSWITH[c] $0', searchTerm);
  • results.sorted(sortKey, [reverse]) - You can also sort any ResultSet by a certain field name, e.g. const sortedResults = searchResults.sorted('serialNumber', true); Each call to any of these querying functions returns a new ResultSet, which you can then call either filtered or sorted on again, recursively, forever. Calling filtered on the ResultSet produced by a call to filtered is the same as putting both filter strings together with an 'AND'.

For modifying

  • database.write(() => { your code }) - Any code that will modify the database needs to be placed inside a call to database.write. The function takes in a callback function as an argument. All code within this function (we just use an arrow function) will be persisted to the database when the write() returns. In this way it is like starting a transaction, executing the callback function, and then ending the transaction.
  • implicit assignment within a write, i.e. databaseObject.fieldName = 'Hello' - Within a write, any time you assign a value to a field of a database object, it will persist to local storage, so you don't need to do anything more explicit to edit the data. For example, database.write(() => {transaction.status = 'finalised';}) would update the transaction's status to finalised and save that to disk. In contrast, transaction.status = 'finalised'; without a write() wrapping it would only update that transaction's status temporarily in memory, until that variable went out of scope.
  • database.save(dataType, object) - This is a Sussol extension that must be called after any series of implicit assignments within a write that updates a database object, to ensure that change listeners are notified of the update
  • database.update(dataType, databaseObject) - Use this function to create or modify a whole database object in one swoop. Even to create a new object, we avoid using database.create in places where it is appropriate for that object to already exist. For this reason .update is used heavily in sync so that changes pushed by the server replace the existing data with the same ID.
  • database.create(dataType, object) - Adds a new object of dataType to the database, with the data described in object. As described above .update is preferable when an object with that ID might already exist - .create will throw an error in this case (there are some cases where we are sure an object with that ID shouldn't exist, in which case it is good to have an error if we try to create a duplicate).
  • database.delete(dataType, objectOrObjects) - Obviously this one deletes the object (or array of objects) from the database. Just to reiterate, none of these function calls will successfully persist the data if they are not wrapped by a database.write.

Internal Functionality of Database Types

Realm provides the ability to define a class that extends Realm.Object, which adds internal functionality to database objects of that type. Each time a database object of that type is brought off disk into memory for access, a new instance of the class you defined is created and returned with the data attached and accessible as though they are fields of the class.

We define a class for the majority of data types. These classes provide access to derived data, e.g. the Item class defines totalQuantity by adding all of the quantities of its ItemBatch children (mobile equivalent of item_lines).

These classes are also where we keep the business logic of the application; as described at the top of this page, instances of each database type are responsible for carrying out any effects of changes to the data. The following is a non-exhaustive list of the most important logic:

Item.addBatchIfUnique, ItemBatch.addTransactionBatchIfUnique, etc.
Adds the given child to the parent, so long as a child with the given id is not already in there

Item.dailyUsage Returns the average daily consumption for this Item over the last three months, by summing the total of each of it's child batches' dailyUsage. See below for how the batches' calculate this.

ItemBatch.dailyUsage
Derived data which returns the average daily consumption for this ItemBatch over the last three months, by summing the consumption in all transactions confirmed within that period. Accounts for recently added ItemBatches by only dividing by the number of days since the earliest transaction, if that was within the last three months

ItemStoreJoin.destructor NameStoreJoin.destructor**
Called when an ItemStoreJoin/NameStoreJoin object is deleted (e.g. by sync), the destructor cleans up by making the related Item/Name no longer visible in the app (in fact this clean up on delete is the only reason we keep ItemStoreJoin and NameStoreJoin records, the actual joining functionality is controlled by the isVisible fields of Item and Name respectively)

MasterListNameJoin.destructor
Called when a MasterListNameJoin object is deleted (e.g. by sync), the destructor cleans up by removing the related MasterList from the related Name (this is the only reason we keep MasterListNameJoin records, the actual joining is maintained in the Name.masterLists field)

NumberSequence.getNextNumber
Gets the next number in the sequence, first looking in its numbersToReuse field, and if none are there, incrementing the current highest number used and returning that

NumberSequence.reuseNumber
Creates a new NumberReuse record with the given number, and adds it to this number sequence's numbersToReuse field (after performing some sanity checks)

Requisition.addItemsFromMasterList
Adds a new RequisitionItem to this requisition for each item in this store's master lists

Requisition.setRequestedToSuggested
Copies all the suggested order values across to be the required valued (i.e. those ordered)

Requisition.pruneRedundantItems
Removes all RequisitionItems with 0 as their required quantity

Requisition.createAutomaticOrder
Calls the three preceding methods in that order, with the result being a requisition of all items that need some amount >0 ordered, with the required quantities all set, ready to finalise. This provides a very fast way to generate and place an order

Requisition.finalise
Prunes off any items that have 0 required quantity, and sets the status to finalised

RequisitionItem.suggestedQuantity
Derives and returns the suggested quantity by multiplying the average daily usage of the related Item by the days to supply of the parent Requisition and subtracting the stock on hand of the related Item. Note that the daily usage and stock on hand are snapshot at the time the Requisition is created.

Stocktake.setItemsById
Sets the stocktake items attached to this stocktake, based on the array of item ids

Stocktake.finalise
Adjusts the inventory by creating an incoming transaction filled with any items that are increased, and an outgoing transaction filled with any items that are decreased, and finalising the transactions so they make the appropriate adjustments to individual items' inventory and deal with all the batch allocation (see the section on dealing with batches). Finally sets the status of the Stocktake to finalised

StocktakeItem.destructor
Ensures the parent Stocktake is not finalised, and throws an error if it is

StocktakeItem.isReducedBelowMinimum
Returns true if this stocktake item's counted quantity would reduce the amount of stock in inventory to negative levels, if it were finalised.

StocktakeItem.applyBatchAdjustments
Because the transaction deals with allocating the adjustments made in a stocktake to the appropriate batches at the time the stocktake is finalised, we needed a way to then take that allocation and apply it to the batches that were snapshot into the stocktake, without unnecessarily duplicating the same allocation logic (which is slightly complex and would easily get out of sync - best to have it in one place.)

Transaction.addItemsFromMasterList
Adds a new TransactionItem to this Transaction for each Item in the associated Name's master lists

Transaction.removeItemsById
Removes the TransactionItems with the given ids from this Transaction, along with all the associated batches

Transaction.confirm
Increases or decreases the associated item batches (depending on whether it is a customer or supplier invoice), and sets the status to confirmed. Given that the app doesn't expose batch information, the transaction uses what I call 'pessimistic FEFO' to increase/decrease the appropriate batches, as a best guess of how the people in the warehouse or clinic would actually have done it. See the section on dealing with batches for details.

Transaction.finalise
First confirms (making the inventory adjustments including appropriate batch allocation) then prunes off any items with 0 total quantity, and sets the status to finalised

TransactionItem.setTotalQuantity
Sets the quantity for the current item by applying the difference to the shortest expiry batches possible, which in turn will adjust inventory if the transaction is confirmed

TransactionBatch.usage
Get the amount of consumption this TransactionBatch represents

TransactionBatch.setTotalquantity
Sets the quantity of this TransactionBatch, and if it belongs to a confirmed Transaction, adjusts inventory accordingly

Change Notifications

The sussol maintained extension to realm, react-native-database was written to add fine-grained change notifications.

This allows class instances to be alerted whenever a database object of interest is created, updated, or deleted. We use this in two key places:

  • Synchronisation - Any time a database object that syncs is modified, we add it to the sync queue
  • Displaying live data - Pages within mSupply Mobile subscribe to be alerted of changes to data types that they display, so that if, e.g., a new customer invoice syncs in, the list of customer invoices updates to display it

To subscribe to notifications of changes:

  • database.addListener(callback) - The callback will be called any time a database object is modified. You probably want to put a conditional statement at the top of the callback to check which data type was changed in order to avoid excessive work every time there is a change to the database. The callback will be passed the following parameters:
    • changeType - Whether the change was a create, update, delete, or wipe
    • dataType - The data type of the modified object
    • databaseObject - The database object that was changed in its new form (unless it was deleted)
    • extraParams - Any extra parameters that were passed through to the database.create, .update, .save, .delete, or .deleteAll call. In mSupply mobile we use this to record whether the source of the modification was a sync coming in from the server, in which case we won't respond to the change by adding it to the sync queue (don't need to push anything back that the server has just pushed down!)
  • database.removeListener(listenerId) - Be sure to call this when a class instance is destroyed, otherwise the callback will be called but will be unavailable to respond, causing a big old error. The listener id is returned by addListener.

Realm now support fine-grained notifications internally, so at some point we should swap over to using their notification system (see the docs). The main flaw with our home-baked solution is that any changes that are made using the implicit 'assignment within a write' do not call the change listeners. Instead we rely on programmers to call database.save as described above. There is an issue for changing to use realm's solution here.

Clone this wiki locally