Extending Inventory with extra data
The inventory
table is the biggest in the database and while adding new columns directly to Inventory
is the fastest (for development and querying), it can quickly become messy with columns that belong to specific types of inventories only (and there's an endless list of those).
In an ideal world, we would have SystemInventory extends Inventory
, SeedInventory extends Inventory
, CryoInventory extends Inventory
, etc. and then have inheritance by table joins. But we unfortunately cannot use formTypeCode
as a @Discriminator
because that would break compatibility with existing databases.
We are looking for a solution that will allow us to add "extra columns" used by only some inventories to some other table. Call it InventoryExtra
. This table can eventually use inheritance (single table or join), but let's start without inheritance.
We would like to store the reference to the last finished viability in InventoryExtra
:
class InventoryExtra {
...
InventoryViability lastViability; // points to last viability test, can be null
}
We don't want to eager join inventory extra, we would probably use a separate endpoint that does a left join
and returns joined results.
Users also are not able to modify extra.lastViability
, it will be handled in the service implementation.
Indexing
We include InventoryExtra
in prepareForIndexing
so it ends up in ES.
If extra
changes, then Inventory must be reindexed, we can use ElasticTrigger
for that.
Unit testing
- Test adding
InventoryExtra
to an inventory - Deleting
Inventory
must deleteInventoryExtra
- Deleting
InventoryExtra
must not delete inventory - Test that we can filter
Inventory
forextra.lastViability.percentViable
Phase 2
An example for phase 2 of an extra is mediaTypeCode
which only applies to some inventories and can serve as an example of upgrading InventoryExtra
to using inheritance:
class TissueCultureExtra extends InventoryExtra {
@CodeValueField
String mediaTypeCode; // type of media used for tissue culture
}