Re: Implementing product attributes

From: Thomas Muller <ttm_at_nextra.com>
Date: 2000/03/22
Message-ID: <A41C4.20752$6b1.370972_at_news1.online.no>#1/1

Thomas Muller <ttm_at_online.no> wrote
> category: (cat_id, cat_name, ...) // PK = cat_id
> product: (prod_id, cat_id, prod_name, ...) // PK = prod_id
> attribute: (attrib_id, cat_id, attrib_name, ...) // PK = attrib_id
> attribute_value: (attrib_id, prod_id, value,...) // PK = attrib_id,
prod_id

This model does not allow a single attribute to span multiple categories. Let's relax this rule by introducing an additional table cat_attrib. The model then looks like this

category: (cat_id, cat_name, ...) // PK = cat_id product: (prod_id, cat_id, prod_name, ...) // PK = prod_id attribute: (attrib_id, attrib_name, ...) // PK = attrib_id cat_attrib: (cat_id, attrib_id) // PK = cat_id, attrib_id attribute_value: (attrib_id, prod_id, value,...) // PK = attrib_id, prod_id

Now, the model does not inherently enforce the (obvious) derived business rule "no product can have an attribute which is not defined for the category the product belongs to". The most low-level way to enforce this rule is probably to execute a trigger before insertion in the attribute_value table (which is the only table coupling attributes and products).

(Oracle)
create or replace trigger enforceProperAttrOnAttribValueTrigger before insert or update of attrib_id, prod_id on attribute_value declare i integer;
begin

    select count(*) into i
    from product p
    where p.prod_id = :new.prod_id

              and p.cat_id = ca.cat_id
              and ca.attrib_id = :new.attrib_id;

    if (i = 0) then raise_application_error(<xxx>, 'Attribute is not defined for the category the product belongs to');

    end if;
end;

A trigger is also necessary when deleting from the cat_attrib table:

(Oracle)
create or replace trigger enforceProperAttrOnCatAttribTrigger before delete on cat_attrib
declare i integer;
begin

    select count(*) into i
    from product p, attrib_value av
    where p.cat_id = :cat_id

              and av.attrib_id = :attrib_id
              and av.prod_id = p.prod_id;

    if (i > 0) then raise_application_error(<xxx>, 'There exists one or more products in the category with values for the attribute');

    end if;
end;

--

Thomas

Received on Wed Mar 22 2000 - 00:00:00 CET