Thread: cross-table constraints?

Hello Postgres Novice Helpers,

I don't know if this is the correct place for this question, but please
redirect as appropriate.

I am trying to create a datastore for a mathematical optimization model
I've built.  The math model data structure has a lot of
interdependencies and data integrity has been a constant battle over the
past year.  I've been made aware that data integrity is the I in ACID,
and is exactly the reason RDBMSs were created.  Great!

I've spent some time putting together a schema that appears to make
sense, taking care of such concepts as "single point of authority" and
"normalization".  I've tried to normalize it as best I could, but I
don't exactly have a lot of practice designing schemas.  I'm now running
into a situation where I can't create a constraint that I think I need.
  My research suggests that the type of constraint I want is
colloquially known as a "cross-table constraint", and implies that I
have not properly normalized or otherwise structured my schema.  So, I
would highly appreciate any pointers this list could give me.

For the sake of the schema below, let me briefly describe what the model
needs and does.

The model is an optimization model, so at it's core finds a maximum or
minimum value of an "objective function", subject to any number of
constraints.  For example, a typical objective function would be "Find
the minimum cost to supply energy to this system."

The model is organized into analyses, and each analysis can have a set
of processes.  I define 'process' as a technology-vintage tuple <t, v>.
  For example, a technology 'e_coal' might describe an electric coal
power plant, and <e_coal, 1980> would describe a coal power plant built
in 1980.

We might define the amount of capacity installed as:

    CAP[e_coal, 1980] = 100    # some number of units

The analysis also has the notion of a first period.  For example, if the
analysis in question had these vintages:

     1980, 1990, 2000, 2010, 2020, 2030, 2040

And period_0 (the first period), was 2010, then the model only makes
decisions about the periods 2010, 2020, and 2030.

This means that to /utilize/ the <e_coal, 1980> process, we might define
some "activity" variables for it:

     ACT[2010, e_coal, 1980]
     ACT[2020, e_coal, 1980]
     ACT[2030, e_coal, 1980]
     ACT[2040, e_coal, 1980]

The ACTivity variable is indexed by period, technology, and vintage
<p,t,v>.  *Note that the set of periods (e.g., 2010, 2020, 2030, 2040)
is a subset of the set of vintages.*

Here's where I get to how this relates to my question: as the model can
only make decisions with variables that describe future time periods
(e.g., ACT[2010,...], ACT[2020,...], ACT[2030,...], ...), it makes no
sense to define any characteristics for periods prior to 2010.  For
example, if the use of the <e_coal, 1980> cost different amounts in
different periods, I might have data like this:

     Cost[2010, e_coal, 1980] = 0.10   # $/kWh
     Cost[2020, e_coal, 1980] = 0.30   # $/kWh
     Cost[2030, e_coal, 1980] = 0.60   # $/kWh
     Cost[2040, e_coal, 1980] = 0.33   # $/kWh

However, what's currently to prevent a user from giving data for 2000,
or 1990?  At the moment, nothing other than my application logic.  For
example, consider this (minimized) schema:

     CREATE TABLE analysis (
       id        INTEGER        NOT NULL PRIMARY KEY,
       name      VARCHAR(32767) NOT NULL UNIQUE,
       period_0  INTEGER        NOT NULL
     );

     CREATE TABLE vintage (
       id          INTEGER NOT NULL PRIMARY KEY,
       analysis_id INTEGER NOT NULL REFERENCES analysis (id),
       vintage     INTEGER NOT NULL
     );

     CREATE TABLE technology (
       id    integer        NOT NULL PRIMARY KEY,
       name  varchar(32767) NOT NULL UNIQUE
     );

     CREATE TABLE process (
       id            INTEGER NOT NULL PRIMARY KEY,
       analysis_id   INTEGER NOT NULL REFERENCES analysis (id),
       technology_id INTEGER NOT NULL REFERENCES technology (id),
       vintage_id    INTEGER NOT NULL REFERENCES vintage (id),

       UNIQUE (analysis_id, technology_id, vintage_id)
     );

     CREATE TABLE cost (
       id          integer NOT NULL PRIMARY KEY,
       period_id   integer NOT NULL REFERENCES vintage (id),
       process_id  integer NOT NULL REFERENCES process (id),
       value       real    NOT NULL,

       UNIQUE (period_id, process_id)
     );

Ideally, I'd have a constraint on the 'cost' table that required that
the period referenced by period_id was not less than the associated
analysis period_0.  I would like any INSERT statement that mimicks this
printf format string would fail:

     INSERT INTO cost (period_id, process_id, value)
               VALUES (%d, %d, %f);

   (where, period_id maps to a vintage.vintage that is less than
   analysis.period_0)

What would be a good way to reorganize my schema to enable a constraint
that effectively limits the allowable rows of cost, subject to the value?

Many thanks (and apologies for the long post!),

Kevin

( See response below. )

On Tue, Aug 27, 2013 at 2:41 PM, Kevin Hunter Kesling <kmhunte2@ncsu.edu> wrote:
> Hello Postgres Novice Helpers,
>
> I don't know if this is the correct place for this question, but please
> redirect as appropriate.
>
> I am trying to create a datastore for a mathematical optimization model I've
> built.  The math model data structure has a lot of interdependencies and
> data integrity has been a constant battle over the past year.  I've been
> made aware that data integrity is the I in ACID, and is exactly the reason
> RDBMSs were created.  Great!
>
> I've spent some time putting together a schema that appears to make sense,
> taking care of such concepts as "single point of authority" and
> "normalization".  I've tried to normalize it as best I could, but I don't
> exactly have a lot of practice designing schemas.  I'm now running into a
> situation where I can't create a constraint that I think I need.  My
> research suggests that the type of constraint I want is colloquially known
> as a "cross-table constraint", and implies that I have not properly
> normalized or otherwise structured my schema.  So, I would highly appreciate
> any pointers this list could give me.
>
> For the sake of the schema below, let me briefly describe what the model
> needs and does.
>
> The model is an optimization model, so at it's core finds a maximum or
> minimum value of an "objective function", subject to any number of
> constraints.  For example, a typical objective function would be "Find the
> minimum cost to supply energy to this system."
>
> The model is organized into analyses, and each analysis can have a set of
> processes.  I define 'process' as a technology-vintage tuple <t, v>.  For
> example, a technology 'e_coal' might describe an electric coal power plant,
> and <e_coal, 1980> would describe a coal power plant built in 1980.
>
> We might define the amount of capacity installed as:
>
>    CAP[e_coal, 1980] = 100    # some number of units
>
> The analysis also has the notion of a first period.  For example, if the
> analysis in question had these vintages:
>
>     1980, 1990, 2000, 2010, 2020, 2030, 2040
>
> And period_0 (the first period), was 2010, then the model only makes
> decisions about the periods 2010, 2020, and 2030.
>
> This means that to /utilize/ the <e_coal, 1980> process, we might define
> some "activity" variables for it:
>
>     ACT[2010, e_coal, 1980]
>     ACT[2020, e_coal, 1980]
>     ACT[2030, e_coal, 1980]
>     ACT[2040, e_coal, 1980]
>
> The ACTivity variable is indexed by period, technology, and vintage <p,t,v>.
> *Note that the set of periods (e.g., 2010, 2020, 2030, 2040) is a subset of
> the set of vintages.*
>
> Here's where I get to how this relates to my question: as the model can only
> make decisions with variables that describe future time periods (e.g.,
> ACT[2010,...], ACT[2020,...], ACT[2030,...], ...), it makes no sense to
> define any characteristics for periods prior to 2010.  For example, if the
> use of the <e_coal, 1980> cost different amounts in different periods, I
> might have data like this:
>
>     Cost[2010, e_coal, 1980] = 0.10   # $/kWh
>     Cost[2020, e_coal, 1980] = 0.30   # $/kWh
>     Cost[2030, e_coal, 1980] = 0.60   # $/kWh
>     Cost[2040, e_coal, 1980] = 0.33   # $/kWh
>
> However, what's currently to prevent a user from giving data for 2000, or
> 1990?  At the moment, nothing other than my application logic.  For example,
> consider this (minimized) schema:
>
>     CREATE TABLE analysis (
>       id        INTEGER        NOT NULL PRIMARY KEY,
>       name      VARCHAR(32767) NOT NULL UNIQUE,
>       period_0  INTEGER        NOT NULL
>     );
>
>     CREATE TABLE vintage (
>       id          INTEGER NOT NULL PRIMARY KEY,
>       analysis_id INTEGER NOT NULL REFERENCES analysis (id),
>       vintage     INTEGER NOT NULL
>     );
>
>     CREATE TABLE technology (
>       id    integer        NOT NULL PRIMARY KEY,
>       name  varchar(32767) NOT NULL UNIQUE
>     );
>
>     CREATE TABLE process (
>       id            INTEGER NOT NULL PRIMARY KEY,
>       analysis_id   INTEGER NOT NULL REFERENCES analysis (id),
>       technology_id INTEGER NOT NULL REFERENCES technology (id),
>       vintage_id    INTEGER NOT NULL REFERENCES vintage (id),
>
>       UNIQUE (analysis_id, technology_id, vintage_id)
>     );
>
>     CREATE TABLE cost (
>       id          integer NOT NULL PRIMARY KEY,
>       period_id   integer NOT NULL REFERENCES vintage (id),
>       process_id  integer NOT NULL REFERENCES process (id),
>       value       real    NOT NULL,
>
>       UNIQUE (period_id, process_id)
>     );
>
> Ideally, I'd have a constraint on the 'cost' table that required that the
> period referenced by period_id was not less than the associated analysis
> period_0.  I would like any INSERT statement that mimicks this printf format
> string would fail:
>
>     INSERT INTO cost (period_id, process_id, value)
>               VALUES (%d, %d, %f);
>
>   (where, period_id maps to a vintage.vintage that is less than
>   analysis.period_0)
>
> What would be a good way to reorganize my schema to enable a constraint that
> effectively limits the allowable rows of cost, subject to the value?
>
> Many thanks (and apologies for the long post!),
>
> Kevin
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Kevin,

I am not a database expert, I don't see a way to enforce what you want
directly in the uniqueness and foreign key constraints of the schema.
But I believe you should be able to enforce the constraint with a
trigger.  http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

I may have misunderstood your example, but I think this _might_ do
what you want:

CREATE FUNCTION cost_period_check() RETURNS trigger AS $cost_period_check$
    DECLARE
       a_row analysis%ROWTYPE;
    BEGIN
       SELECT INTO a_row analysis.* FROM analysis
             INNER JOIN process ON process.analysis_id = analysis.id
             WHERE process.id = NEW.process_id LIMIT 1;
       IF a_row.period_0 > NEW.period_id THEN
            RAISE EXCEPTION '% is before analysis period_0 %',
NEW.period_id, a_row.period_0;
       END IF;
        RETURN NEW;
    END;
$cost_period_check$ LANGUAGE plpgsql;

CREATE TRIGGER cost_trigger BEFORE INSERT OR UPDATE ON cost
    FOR EACH ROW EXECUTE PROCEDURE cost_period_check();

Does that help?
-Mike

Kevin Hunter Kesling wrote
>      CREATE TABLE cost (
>        id          integer NOT NULL PRIMARY KEY,
>        period_id   integer NOT NULL REFERENCES vintage (id),
>        process_id  integer NOT NULL REFERENCES process (id),
>        value       real    NOT NULL,
>
>        UNIQUE (period_id, process_id)
>      );

A check constraint can only reference the same table as on which it is
defined so you will most likely, in some place, define either a trigger or
wrap the relevant constraint checking into an API function and only modify
the relevant database items via that function.

That said you can create a relation containing all the valid combinations
and then use a foreign key constraint on the cost side so that only defined
combinations can be costed out.

Two other comments to consider.  The "cost" relation defined above, if you
de-normalize it via the foreign keys, ends up having two "analysis_id"
fields - the one on vintage and the one on process.  It is possible that
these two field may not be in sync - in addition to the "minimum period"
error you already have identified.  The reason for this problem is that you
are using artificial keys for your relationships instead of natural keys.

I may espouse on this more later but cannot at the moment.  While surrogate
keys are not evil they are also not something to be used lightly and in as
complex a model as this using natural keys does have its advantages.  Since
you can define multi-column foreign keys the same analysis_id on the cost
table can be related to other tables in two separately defined "references".

Something to ponder and it may help solve both problems.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/cross-table-constraints-tp5768724p5768733.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

At 3:05pm -0400 Tue, 27 Aug 2013, Michael Swierczek wrote:
>> What would be a good way to reorganize my schema to enable a
>> constraint that effectively limits the allowable rows of cost,
>> subject to the value [from another table and column]?

> I am not a database expert, I don't see a way to enforce what you
> want directly in the uniqueness and foreign key constraints of the
> schema. But I believe you should be able to enforce the constraint
> with a trigger. http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>
> I may have misunderstood your example, but I think this _might_ do
> what you want:
>
> CREATE FUNCTION [...]

> CREATE TRIGGER cost_trigger BEFORE INSERT OR UPDATE ON cost
>      FOR EACH ROW EXECUTE PROCEDURE cost_period_check();

Thank you very much for this example code.  That is a couple hours of
tinkering/learning now reduced to changing a couple of characters.

> Does that help?

Indeed it does.  I was hoping to avoid a trigger, and that I was simply
missing some requisite understanding of RDB internals and the SQL
language.  Alas.

Thanks!

Kevin

At 3:33pm -0400 Tue, 27 Aug 2013, David Johnston wrote:
> Kevin Hunter Kesling wrote
>>   CREATE TABLE cost (
>>     id          integer NOT NULL PRIMARY KEY,
>>     period_id   integer NOT NULL REFERENCES vintage (id),
>>     process_id  integer NOT NULL REFERENCES process (id),
>>     value       real    NOT NULL,
>>
>>     UNIQUE (period_id, process_id)
>>   );
>
> A check constraint can only reference the same table as on which it
> is defined so you will most likely, in some place, define either a
> trigger or wrap the relevant constraint checking into an API function
> and only modify the relevant database items via that function.
>
> That said you can create a relation containing all the valid
> combinations and then use a foreign key constraint on the cost side
> so that onlydefined combinations can be costed out.

I had thought about that, but that would require transforming the
trigger logic from the check constraint to the creation of rows in the
"valid" table.  Is that a more "correct" approach then simply performing
the check via a trigger on the cost table?  If so, why?

> Two other comments to consider. The "cost" relation defined above, if
> you de-normalize it via the foreign keys, ends up having two
> "analysis_id" fields - the one on vintage and the one on process.

This is a really good observation!  Thank you for pointing it out.

> It is possible that these two field may not be in sync - in addition
> to the "minimum period" error you already have identified.

Heh, while I will do my utmost to get my application logic correction,
this is a constant worry for me.  It would be /really/ nice to be able
to figure this out at the DB level.  Hopefully I can work around this
with triggers.

> The reason for this problem is that you are using artificial keys
> for your relationships instead of natural keys.
>
> I may espouse on this more later but cannot at the moment. While
> surrogate keys are not evil they are also not something to be used
> lightly and in as complex a model as this using natural keys does
> have its advantages. Since you can define multi-column foreign keys
> the same analysis_id on the cost table can be related to other tables
> in two separately defined  "references".

I really like this point.  I really do.  Unfortunately, the large detail
that I left out from my question is that I've created this model through
Django, which (currently) requires a single column be the primary key
for it's ORM logic.

     [1] "Each model requires exactly one field to have
          primary_key=True."  (where 'field' = DB column)

     [2] Currently Django models only support a single column in this
         set, denying many designs where the natural primary key of a
         table is multiple columns. Django currently can't work with
         these schemas; they must instead introduce a redundant
         single-column key (a “surrogate” key), forcing applications
         to make arbitrary and otherwise-unnecessary choices about
         which key to use for the table in any given instance.

The choice of Django has drastically helped my project in other areas,
but leaves some of the data integrity and modeling issues to the
application logic.  I suppose "one pays their monies and makes their
choices!"

Thank you very much for your helpful insights.

Kevin

[1]
https://docs.djangoproject.com/en/dev/topics/db/models/#automatic-primary-key-fields

[2] https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys