Logical Design Question

Sorry for the long post but there is no way short way to explain this. So
read only if you have the time. If you take the time, I really, really
appreciate it.
Bill
I have a logical design situation that seems not to fit well in the
relational model (at least the way I am doing it – with no help from
triggers). I work for a group of commercial photographers who are contracte
d
to photograph events (graduations, races, fraternity social, etc.). They
offer Packages to their customers. A Package may consist of {4-5x7’s,
2-8x10’s, 16-Wallets}. The customer can choose the package as is or apply
an
Option to the package for a small additional price. An option is Black &
White, Sepia (antique), or Colorize. There are many options, but the
example I just gave is an Option Set from which only one selection can be
made. Another Option Set might consist of border styles to be applied aroun
d
each photo.
An option set and all its subsets are possible way to construct the options
that will be presented to the customer. For example, the example I just gav
e
you has 3 members. But there are 6 additional subsets of this set not
counting the empty set. So the maximum set of options and all its subsets
form an OptionGroup. Only one OptionSet from within an OptionGroup is
allowed to be set up as an option for Package.
Furthermore, an option can belong to only one OptionGroup. But, as
explained above, an option can belong to many OptionSet’s.
Here is the problem:
Given a Package table consisting of Package_ID as the PK, I need to
associate one or more OptionSet’s. I can associate multiple optional sets
to
the same package as long as they are from different OptionGroup’s.
If that associative table consists of Package_ID, OptionGroup_ID,
OptionSet_ID , Price columns with the first three columns as the PK, I
violate the business rule that only one OptionSet from the same OptionGroup
can be associated to the same package.
If I make Package_ID, OptionGroup_ID as the Primary key , I can enforce this
rule but the other relationships look weird. Here is what I came up with (i
f
you model these tables you will see the weirdness) . . .
OptionGroup (table)
OptionGroup_ID (PK)
OptionGroupName
OptionSet (table)
OptionSet_ID (PK)
OptionGroup_ID (FK) (non-identifying relationship with OptionGroup table)
Option_OptionSet (table)
OptionSet_ID (PK, FK) (identitying relationship with OptionSet table)
Option_ID (PK, FK) (identifying relationship with Option table)
OptionGroup_ID (FK) (non-identifying relationship with OptionGroup table)
Option (table)
Option_ID (PK)
OptionName
Package (table)
Package_ID (PK)
PackageName
PackageOptionSet (table)
Package_ID (PK, FK) (identifying relationship with Package table)
OptionGroup_ID (PK, FK) (identifying relationship with OptionGroup table)
OptionSet_ID (FK) (non-identifying relationship with OptionSet table)
PriceHave you ever been to this site:
http://www.datamodel.org/
ML
p.s. sometimes it's up, sometimes it's down...|||Well apparently it is down now, in any case, I prefer MSDN Groups.
"ML" wrote:

> Have you ever been to this site:
> http://www.datamodel.org/
>
> ML
> p.s. sometimes it's up, sometimes it's down...|||This might also help:
http://www.agiledata.org/essays/dataModeling101.html
ML|||I know how to model data. This is a particularly hard one to model, that is
why I am asking the question. I am looking for another's modeler's
perspective on this particular problem not how to model data.
"ML" wrote:

> This might also help:
> http://www.agiledata.org/essays/dataModeling101.html
>
> ML|||I'd choose an identifying relationship:
OptionSet (table)
OptionGroup_ID (PK,FK) (identifying relationship with OptionGroup
table)
OptionSet_ID (PK)|||You need to isolate relationships in your proposed Option_OptionSet and
PackageOptionSet tables. It seems you started designing tables before you
laid out the entire logical model.
Try something like this (<-- = 'one to many'):
1)
Package <-- Photo <-- OptionSet --> OptionGroup
OptionSet <-- Option
or:
Package <-- Photo
Package <-- OptionSet --> OptionGroup
OptionSet <-- Option
2)
Photographer <-- Customer (don't know if you really need this)
Customer <-- Package
ML|||ML, AK,
Using your notation (< is the one side and -- is the many side), the correct
relationships are:
OptionGroup<--OptionSet <-- Option
Package <-- PackageOptionSet --> OptionGroup
The problem with the first relationship is that you can have groups 1 and 2
which is ok. You can have options A and B which is also OK. You can have
1,A and 2,A in OptionSet which not OK. An option can only belong to one
group.
Assuming we have Options assigned to only one group we still have a problem
with the second relationship. For example, suppose we have the OptionSet
{1,A; 1,B; 1, C} called OS1 and OptionSet{1,A; 1,B} called OS2 (the first
number is the group id and the 2nd number is the Option id). This is OK.
The 2nd relationship permits the record -- P1,1,OS1 and the record --
P1,1,OS2 in PackageOptionSet which is not Ok. Only one option set from a
group can be assigned to a Package (P1 is the Package ID, 1 is the Group ID,
and OS1 or OS2 is the assigned option set.)
The above situation is an example of offering the Options of Black & White,
Sepia, and Colorize for one package but only offering Black & White, Sepia
for another package. You don't want the possibility of both option sets be
assigned to a package.
Bill
"ML" wrote:

> You need to isolate relationships in your proposed Option_OptionSet and
> PackageOptionSet tables. It seems you started designing tables before you
> laid out the entire logical model.
> Try something like this (<-- = 'one to many'):
> 1)
> Package <-- Photo <-- OptionSet --> OptionGroup
> OptionSet <-- Option
> or:
> Package <-- Photo
> Package <-- OptionSet --> OptionGroup
> OptionSet <-- Option
> 2)
> Photographer <-- Customer (don't know if you really need this)
> Customer <-- Package
>
> ML|||In general, requesting solutions for data design problems in newsgroups is
somewhat risky since there is a fair chance that several aspects of the
business model is misunderstood and miscommunicated. Since your exact
requirements are transparent to others in this group the suggested solutions
can sometimes be misguided as well. If this is a critical project, I would
suggest you s the help of an experienced and trained professional who can
have direct access to your business model and data requirements.
With that preface, can you explain your tables a bit more elaborately? Based
on what I understood, Optiongroup is just a derivable resultset out of
existing data, right? Why not use a view for displaying the data to the
customers? Is the set of options time-varying -- if not, based on your
descriptions, aren't the options really attributes rather than entities?
Anith|||I know it is very hard to understand business rules communicated in this way
and just as hard to figure out a way to present the rules. While it is
obvious to me, that is because I have worked this busines so long.
An OptionGroup is not a derviable resultset. Options exists independently
and some are mutually exclusive for example you can't have Black & White and
Sepia on the same photo.
For example one OptionGroup ( call it the Tone Group) consist of these
OptionSets:
{BW, Sep, Cz}
{BW, Sep}
{BW, CZ}
{Sep, CZ}
{BW}
{Sep}
{CZ}
Only one of these sets can be assigned to an Package. The concept of an
OptionGroup came into being soley as way to facilitate this rule. If all
these OptionSets belong to a the same group, the application and database
have a way to know that only one option set from a group can be assigned to
a
package. Also the OptionGroup facilitate the user being able to offer
different combinations of the same options.
The concept of a OptionSet came into being as way for the application to
know that when presenting a set of options from the same option set only one
choice is allowed.
Suppose you have another Option Group called Border consisting of:
{FC, BN}
{FC}
{BN}
It is permissble to assign {BW, Sep} and {FC} as option sets. The
application know these are from different groups. At order taking time, the
application knows to present BW and SEP as a one choice and FC as a another
choice. So the customer can choose (BW and FC ) or (SEP and FC) or (just BW
or just FC) but not (BW and Sep).
I hope this helps
"Anith Sen" wrote:

> In general, requesting solutions for data design problems in newsgroups is
> somewhat risky since there is a fair chance that several aspects of the
> business model is misunderstood and miscommunicated. Since your exact
> requirements are transparent to others in this group the suggested solutio
ns
> can sometimes be misguided as well. If this is a critical project, I would
> suggest you s the help of an experienced and trained professional who c
an
> have direct access to your business model and data requirements.
> With that preface, can you explain your tables a bit more elaborately? Bas
ed
> on what I understood, Optiongroup is just a derivable resultset out of
> existing data, right? Why not use a view for displaying the data to the
> customers? Is the set of options time-varying -- if not, based on your
> descriptions, aren't the options really attributes rather than entities?
> --
> Anith
>
>