You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Mark Raynsford <li...@io7m.com> on 2021/08/11 14:31:38 UTC
Difficulty enforcing a table invariant
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
Re: Difficulty enforcing a table invariant
Posted by Mark Raynsford <li...@io7m.com>.
On 2021-08-12T07:58:57 +0000
Mark Raynsford <li...@io7m.com> wrote:
>
> Each row of items table describes a class of item, and indicates how
> many of that class of item we have. Same goes for the item_locations
> table; each row of the table indicates how many of each class of item
> are in each storage location.
I'm open to suggestions as to other ways to model this in terms of
tables.
The inventory system is primarily concerned with sets of items, because
the individual items don't have meaningful identities of their own
(an individual 4.7kohm resistor is exactly the same as any other
4.7kohm resistor in the same batch from the same manufacturer).
The inventory system has to track "How many of each type of item should
I have?" and "Of all of the items I'm supposed to have, where are they
stored?".
Obviously a useful invariant is that the number of stored items should
equal the number of items that you think you're supposed to have. :)
--
Mark Raynsford | https://www.io7m.com
Re: Difficulty enforcing a table invariant
Posted by Mark Raynsford <li...@io7m.com>.
On 2021-08-11T13:35:31 -0700
Rick Hillegas <ri...@gmail.com> wrote:
> Triggers sound like the right tool for the job. I can't offer any more
> specific advice. I don't understand why item_count and
> item_location_count are hard-coded into the tables rather than being
> constructed on the fly by queries.
Hello!
There might be a misunderstanding with regards to the data model.
Each row of items table describes a class of item, and indicates how
many of that class of item we have. Same goes for the item_locations
table; each row of the table indicates how many of each class of item
are in each storage location.
If, instead, the model was "each row of the items table describes
a single item", then yes, there'd be no item_count column and the
total would be calculated by counting the rows.
--
Mark Raynsford | https://www.io7m.com
Re: Difficulty enforcing a table invariant
Posted by Rick Hillegas <ri...@gmail.com>.
Triggers sound like the right tool for the job. I can't offer any more
specific advice. I don't understand why item_count and
item_location_count are hard-coded into the tables rather than being
constructed on the fly by queries.
On 8/11/21 7:31 AM, Mark Raynsford wrote:
> 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...
>