Re: cross-table constraints?

( 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