Hello. I have the following tables:
---
create table cardant.items (
item_id char(16) for bit data not null primary key,
item_name varchar(128) not null,
item_count bigint not null,
constraint check_natural_count check (item_count >= 0)
)
create table cardant.locations (
location_id char(16) for bit data not null primary key,
location_description varchar(256) not null
)
create table cardant.item_locations (
item_location_id char(16) for bit data not null,
item_location_item_id char(16) for bit data not null,
item_location_count bigint not null,
constraint location_exists foreign key (item_location_id) references
cardant.locations (location_id),
constraint item_exists foreign key (item_location_item_id) references
cardant.items (item_id),
constraint check_location_natural_count check (item_location_count >= 0)
)
---
These are tables for an inventory system. Each row of the
items table describes a collection of items, so an example
row might be ("c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", "4.7Ω Resistor", 100).
Each row of the locations table describes a storage location,
so an example row might be ("a7e0ab099d18b104dd673b01325053ba", "Box 1A").
Each row of the item_locations table associates items with
storage locations. The relationship is defined such that
items may be split between storage locations, so:
Given items:
("c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", "4.7Ω Resistor", 100)
... and storage locations:
("a7e0ab099d18b104dd673b01325053ba", "Box 1A")
("f45a88bb90d7cc1e492d26f7a34c3041", "Box 1B")
We might have this in the item_locations table:
("a7e0ab099d18b104dd673b01325053ba", "c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", 50)
("f45a88bb90d7cc1e492d26f7a34c3041", "c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", 50)
That would indicate that 50 of the resistors are in Box 1A
and 50 are in Box 1B.
Now what I want to do is enforce the invariant that:
for item_id in items:
sum (item_location_count) where item_location_item_id = item_id
== item_count
In other words, for a given item ID, the sum of the items in
the storage locations should sum to the value given in the
items table. We don't want to get into a situation where the
items table says we have 100 resistors, but the items_locations
table says that there are 100 of them in Box 1A and 100 of
them in Box 1B!
The problem I'm having is that expressing this goes beyond
what can be expressed in a check constraint, which almost
certainly means expressing it as one or more triggers. The further
problem I'm then having is that it feels like there needs
to be a trigger on both the items and item_locations
tables, and neither statement or row triggers really seem
to apply; by definition I'm going to have to update multiple
rows in multiple tables each time to maintain the invariant.
Is there some better way I could be doing this? Maybe even
some other way I could be defining the tables...
--
Mark Raynsford | https://www.io7m.com
pgpUGIgRVeSQh.pgp
Description: OpenPGP digital signature
