design issues

[Sean Newton]

Hi all, I was wondering how most people handle a particular design issue.
Say you have products with options and

choices for those options. T-Shirt (product) size (option) s, m, l, xl
(choices). Basically a system with

multi-value attributes so the physical implementation is so:

products
--------
id
name

options
-------
id
productID
name

choices
-------
optionid
value

certainly this is greatly simplified but is hopefully enough to get the
point across for a couple questions.

this model is great for static stuff, but how do you handle changes? Most
especially, when creating

administrative functionality that allows for changing options and choices.
taking the above example, suppose they

want to modify the t-shirt product: delete small, add xxl, and rename them
to 'medium', 'large', etc. I see two

ways: either create some monstrous code that keeps track of what is supposed
to be updated, deleted and added,

and another way that just deletes all previous entries and regenerates new
ones.

I lean towards and prefer the latter option.

example:

product 1000 (t-shirt) related to option 2000 (size) has four choices 3000,
3001, 3002 (s, m, l, xl)

when changed, this becomes:

product 1000 (t-shirt) related to option 2001 (size) has four choice 3003,
3004, 3005 ('medium', 'large',

'x-large', 'xx-large')

this sounds great but... what happens if other data is dependent? for
instance, persisted shopping baskets and

orders? currently, my solutions is to wipe out any shopping baskets that had
a product that gets modified and

orders is not that big of a deal as they are stored highly unnormalized with
a 'soft' (no actual fk constraint)

id reference to products.

then again, even if you create the monstrous code to track changes, there is
no guarantee the the shopping basket

would actually refer to the proper choice anymore. unless some history
tracking is used (even more monstrous

code)

was just wondering how other handle some of these design issues.

-sean

[James Hokes]

To keep your RI intact, don't delete. Add an "Active" flag to the table if
you need to disable certain attributes. That way, your integrity is
maintained, but users won't be able to choose unavailable options in the
front-end.

James Hokes

"Sean Newton" <BoulderSean2002@yahoo.com> wrote in message
news:3db45798$1@tnews.web.devx.com...
> Hi all, I was wondering how most people handle a particular design issue.
> Say you have products with options and
>
> choices for those options. T-Shirt (product) size (option) s, m, l, xl
> (choices). Basically a system with
>
> multi-value attributes so the physical implementation is so:
>
> products
> --------
> id
> name
>
> options
> -------
> id
> productID
> name
>
> choices
> -------
> optionid
> value
>
> certainly this is greatly simplified but is hopefully enough to get the
> point across for a couple questions.
>
> this model is great for static stuff, but how do you handle changes? Most
> especially, when creating
>
> administrative functionality that allows for changing options and choices.
> taking the above example, suppose they
>
> want to modify the t-shirt product: delete small, add xxl, and rename them
> to 'medium', 'large', etc. I see two
>
> ways: either create some monstrous code that keeps track of what is
supposed
> to be updated, deleted and added,
>
> and another way that just deletes all previous entries and regenerates new
> ones.
>
> I lean towards and prefer the latter option.
>
> example:
>
> product 1000 (t-shirt) related to option 2000 (size) has four choices
3000,
> 3001, 3002 (s, m, l, xl)
>
> when changed, this becomes:
>
> product 1000 (t-shirt) related to option 2001 (size) has four choice 3003,
> 3004, 3005 ('medium', 'large',
>
> 'x-large', 'xx-large')
>
> this sounds great but... what happens if other data is dependent? for
> instance, persisted shopping baskets and
>
> orders? currently, my solutions is to wipe out any shopping baskets that
had
> a product that gets modified and
>
> orders is not that big of a deal as they are stored highly unnormalized
with
> a 'soft' (no actual fk constraint)
>
> id reference to products.
>
> then again, even if you create the monstrous code to track changes, there
is
> no guarantee the the shopping basket
>
> would actually refer to the proper choice anymore. unless some history
> tracking is used (even more monstrous
>
> code)
>
> was just wondering how other handle some of these design issues.
>
> -sean
>
>