[Dbix-class] more complex relationships

[Mark Hedges]

On Wed, 22 Mar 2006, Brandon Black wrote:

> On 3/22/06, Mark Hedges <hedges at ucsd.edu> wrote:
> > Here's an example that can't
> > be done:
> >
> >   product         pk product_code
> >
> >   vendor          pk vendor_name
> >
> >   vendor_product  pk vendor_name, product_id
> >                   fk product_code
> >
> >   sale            pk sale_id
> >                   ff vendor_name
> >
> >   sale_item       pk sale_item_id
> >                   fk sale_id
> >                   ff product_id
> >
> 
> Could you show that in SQL create statements?  This example 
> seems to me like it is something one should be able to 
> accomplish via existing DBIC relationships, unless there's 
> some kind of strange normalization problem going on here, but 
> I just wanted to make sure we're on the same page wrt to what 
> the above means, exactly.

Something like this:

CREATE TABLE product (
    product_code        ENUM('XX','YY')                 NOT NULL,
    description         TEXT,
    PRIMARY KEY ( product_code )
);

CREATE TABLE vendor (
    vendor_name         VARCHAR(24)                     NOT NULL,
    PRIMARY KEY ( vendor_name )
);

CREATE TABLE vendor_product (   -- our products for sale at vendors
    vendor_name         VARCHAR(24)                     NOT NULL,
    product_id          VARCHAR(24)                     NOT NULL,

    product_code        ENUM('XX',YY')                  NOT NULL,

    PRIMARY KEY ( vendor_name, product_id )
);

CREATE TABLE sale (     -- i.e. invoice
    sale_id             SERIAL,

    vendor_name         VARCHAR(24)                     NOT NULL,

    -- customer record foreign key, etc.
    PRIMARY KEY ( sale_id )
);

CREATE TABLE sale_item (    -- item on the invoice
    sale_item_id        SERIAL,

    sale_id             BIGINT UNSIGNED                 NOT NULL,

    product_id          VARCHAR(24)                     NOT NULL,

    PRIMARY KEY ( sale_item_id )
);

So to get from a sale_item to a vendor_product, I have to join
through the sale table to get the vendor_name.  To get from
a sale to all vendor_products sold as items, I already know
the vendor_name, but I have to join against all the product_id's
for each item.  So it's a 2-way join, so to speak.  The PK
of the vendor_product is split between the sale and sale_item.

There's no reason why I would have to save the vendor_name in
the sale_item table, because it's already in the item's parent,
the sale.

Then go back and look at the "what I wanted to do" part of my
original e-mail.  Or if Matt cares to chime in.

Mark