[thelist] Tables referencing each other

Bill Moseley moseley at hank.org
Wed Mar 25 15:31:20 CDT 2009


On Wed, Mar 25, 2009 at 03:18:53PM -0400, Matt Warden wrote:
> On Wed, Mar 25, 2009 at 1:32 PM, Bill Moseley <moseley at hank.org> wrote:
> >        current_profile integer NOT NULL
> 
> This is an example of problems that can be created from data modeling
> issues. You have an attribute in your customer table that describes
> the active/current status of a profile. Move this status information
> to the profile table itself.

Hi Matt,

Are saying instead of having the customer table reference the
"current" profile, have the profile table reference the customer table
and include a flag that says "is_current"?

    CREATE TABLE profile (
        id          SERIAL PRIMARY KEY,
        foo         integer, -- whatever
        cust        integer NOT NULL REFERENCES cust(id),
        is_current  boolean NOT NULL
    );

Then a customer's "current" profile is:

    SELECT
        *
    FROM
        cust
        JOIN profile ON
            cust.id = profile.cust
            AND profile.is_current IS TRUE;


Thanks,


-- 
Bill Moseley.
moseley at hank.org
Sent from my iMutt



More information about the thelist mailing list