Hi all,

Please bear with me I've tried to make my question as terse as possible,
whilst not appearing confusing (hopefully I've not failed in this regard).

I'm trying to store a Chart configuration in an Oracle database
(although I'm hoping that my choice of database will not lead to a
solution specific to it, since so far my database is not platform, or
database specific).

One of these chart entities has two or more axes (one x and at least one
y) related to it, and each axis relates to zero or more plot entities
(which store plot information such as plot type, line or columns/bars
etc). these plot entities relate to a specific item of data that can be
plotted (and relates to a Unit table).

So far I have

relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem

(I'm assuming I've got this relationship right)

How can I now insert a relationship with a Unit entity so that I can
ensure that Axis and Plot can refer to any unit in the unit relation *so
long as its the same Unit*. I'd like to enforce at a relational level a
limitation so that any number of plots can be configured for a specific
axis so long as they are all of the same unit. For instance income and
outgoings can both be related to an axis since they share the same unit
of currency. But weight and income cannot be plots that relate to one
axis since there are two units here and an axis can refer to only one unit.

It's easy to enforce this in my application but I was hoping to somehow
put in some kind of constraint at the database level to stop people
setting up charts with an axis that allows income, weight, and time to
all be plotted on the same axis.

Perhaps someone has stored a chart configuration in a database before
and can offer me some insight.

Many thanks,

Giraffe.

Giraffe wrote:

> Hi all,
>
> Please bear with me I've tried to make my question as terse as possible,
> whilst not appearing confusing (hopefully I've not failed in this regard).
>
> I'm trying to store a Chart configuration in an Oracle database
> (although I'm hoping that my choice of database will not lead to a
> solution specific to it, since so far my database is not platform, or
> database specific).
>
> One of these chart entities has two or more axes (one x and at least one
> y) related to it, and each axis relates to zero or more plot entities
> (which store plot information such as plot type, line or columns/bars
> etc). these plot entities relate to a specific item of data that can be
> plotted (and relates to a Unit table).
>
> So far I have
>
> relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem
>
> (I'm assuming I've got this relationship right)
>
> How can I now insert a relationship with a Unit entity so that I can
> ensure that Axis and Plot can refer to any unit in the unit relation *so
> long as its the same Unit*. I'd like to enforce at a relational level a
> limitation so that any number of plots can be configured for a specific
> axis so long as they are all of the same unit. For instance income and
> outgoings can both be related to an axis since they share the same unit
> of currency. But weight and income cannot be plots that relate to one
> axis since there are two units here and an axis can refer to only one unit.
>
> It's easy to enforce this in my application but I was hoping to somehow
> put in some kind of constraint at the database level to stop people
> setting up charts with an axis that allows income, weight, and time to
> all be plotted on the same axis.
>
> Perhaps someone has stored a chart configuration in a database before
> and can offer me some insight.
>
> Many thanks,

You may have a pragmatic argument for relaxing irreducibility and
simplicity in your choice of candidate key for Axis.

Giraffe wrote:
> Hi all,
>
> Please bear with me I've tried to make my question as terse as possible,
> whilst not appearing confusing (hopefully I've not failed in this regard).
>
> I'm trying to store a Chart configuration in an Oracle database
> (although I'm hoping that my choice of database will not lead to a
> solution specific to it, since so far my database is not platform, or
> database specific).
>
> One of these chart entities has two or more axes (one x and at least one
> y) related to it, and each axis relates to zero or more plot entities
> (which store plot information such as plot type, line or columns/bars
> etc). these plot entities relate to a specific item of data that can be
> plotted (and relates to a Unit table).
>
> So far I have
>
> relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem
>
> (I'm assuming I've got this relationship right)
>
> How can I now insert a relationship with a Unit entity so that I can
> ensure that Axis and Plot can refer to any unit in the unit relation *so
> long as its the same Unit*. I'd like to enforce at a relational level a
> limitation so that any number of plots can be configured for a specific
> axis so long as they are all of the same unit. For instance income and
> outgoings can both be related to an axis since they share the same unit
> of currency. But weight and income cannot be plots that relate to one
> axis since there are two units here and an axis can refer to only one unit.
>
> It's easy to enforce this in my application but I was hoping to somehow
> put in some kind of constraint at the database level to stop people
> setting up charts with an axis that allows income, weight, and time to
> all be plotted on the same axis.
>
> Perhaps someone has stored a chart configuration in a database before
> and can offer me some insight.
>
> Many thanks,
>
> Giraffe.

Trying to be platform neutral is a recipe for unscalable and lousy
performance. Build 95% of the product to be neutral and then, as Oracle
does, build a comptability layer that allows you to use best practices
on the system.

If you want charting don't reinvent the wheel. Look to what you've
already purchase: Oracle Spatial.

http://www.oracle.com/pls/db102/ranked?word=SDO
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> Giraffe wrote:
>
>> Hi all,
>>
>> Please bear with me I've tried to make my question as terse as possible,
>> whilst not appearing confusing (hopefully I've not failed in this
>> regard).
>>
>> I'm trying to store a Chart configuration in an Oracle database
>> (although I'm hoping that my choice of database will not lead to a
>> solution specific to it, since so far my database is not platform, or
>> database specific).
>>
>> One of these chart entities has two or more axes (one x and at least one
>> y) related to it, and each axis relates to zero or more plot entities
>> (which store plot information such as plot type, line or columns/bars
>> etc). these plot entities relate to a specific item of data that can be
>> plotted (and relates to a Unit table).
>>
>> So far I have
>>
>> relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem
>>
>> (I'm assuming I've got this relationship right)
>>
>> How can I now insert a relationship with a Unit entity so that I can
>> ensure that Axis and Plot can refer to any unit in the unit relation *so
>> long as its the same Unit*. I'd like to enforce at a relational level a
>> limitation so that any number of plots can be configured for a specific
>> axis so long as they are all of the same unit. For instance income and
>> outgoings can both be related to an axis since they share the same unit
>> of currency. But weight and income cannot be plots that relate to one
>> axis since there are two units here and an axis can refer to only one
>> unit.
>>
>> It's easy to enforce this in my application but I was hoping to somehow
>> put in some kind of constraint at the database level to stop people
>> setting up charts with an axis that allows income, weight, and time to
>> all be plotted on the same axis.
>>
>> Perhaps someone has stored a chart configuration in a database before
>> and can offer me some insight.
>>
>> Many thanks,
>>
>> Giraffe.
>
>
> Trying to be platform neutral is a recipe for unscalable and lousy
> performance. Build 95% of the product to be neutral and then, as Oracle
> does, build a comptability layer that allows you to use best practices
> on the system.
>
> If you want charting don't reinvent the wheel. Look to what you've
> already purchase: Oracle Spatial.
>
> http://www.oracle.com/pls/db102/ranked?word=SDO

Ugh! Are you seriously recommending the network data model?

Bob Badour wrote:
> DA Morgan wrote:
>> Giraffe wrote:
>>
>>> Hi all,
>>>
>>> Please bear with me I've tried to make my question as terse as possible,
>>> whilst not appearing confusing (hopefully I've not failed in this
>>> regard).
>>>
>>> I'm trying to store a Chart configuration in an Oracle database
>>> (although I'm hoping that my choice of database will not lead to a
>>> solution specific to it, since so far my database is not platform, or
>>> database specific).
>>>
>>> One of these chart entities has two or more axes (one x and at least one
>>> y) related to it, and each axis relates to zero or more plot entities
>>> (which store plot information such as plot type, line or columns/bars
>>> etc). these plot entities relate to a specific item of data that can be
>>> plotted (and relates to a Unit table).
>>>
>>> So far I have
>>>
>>> relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem
>>>
>>> (I'm assuming I've got this relationship right)
>>>
>>> How can I now insert a relationship with a Unit entity so that I can
>>> ensure that Axis and Plot can refer to any unit in the unit relation *so
>>> long as its the same Unit*. I'd like to enforce at a relational level a
>>> limitation so that any number of plots can be configured for a specific
>>> axis so long as they are all of the same unit. For instance income and
>>> outgoings can both be related to an axis since they share the same unit
>>> of currency. But weight and income cannot be plots that relate to one
>>> axis since there are two units here and an axis can refer to only one
>>> unit.
>>>
>>> It's easy to enforce this in my application but I was hoping to somehow
>>> put in some kind of constraint at the database level to stop people
>>> setting up charts with an axis that allows income, weight, and time to
>>> all be plotted on the same axis.
>>>
>>> Perhaps someone has stored a chart configuration in a database before
>>> and can offer me some insight.
>>>
>>> Many thanks,
>>>
>>> Giraffe.
>>
>>
>> Trying to be platform neutral is a recipe for unscalable and lousy
>> performance. Build 95% of the product to be neutral and then, as Oracle
>> does, build a comptability layer that allows you to use best practices
>> on the system.
>>
>> If you want charting don't reinvent the wheel. Look to what you've
>> already purchase: Oracle Spatial.
>>
>> http://www.oracle.com/pls/db102/ranked?word=SDO
>
> Ugh! Are you seriously recommending the network data model?

No!
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Sat, 12 Aug 2006 17:15:27 GMT, Bob Badour
<bbadour@pei.sympatico.ca> wrote:

>Ugh! Are you seriously recommending the network data model?

He is recommending not inventing the wheel.
And he is spot on about 'database neutral apps'
If they have been developed on sqlserver, they just won't work on
Oracle.

--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker wrote:

> On Sat, 12 Aug 2006 17:15:27 GMT, Bob Badour
> <bbadour@pei.sympatico.ca> wrote:
>
>>Ugh! Are you seriously recommending the network data model?
>
> He is recommending not inventing the wheel.

No, he is recommending replacing the wheel with a travois. Just because
a 'solution' exists doesn't mean it is worthwhile.

What he wrote and the link he provided in no way help the original
poster with his question.

Bob Badour wrote:
> Sybrand Bakker wrote:
>
>> On Sat, 12 Aug 2006 17:15:27 GMT, Bob Badour
>> <bbadour@pei.sympatico.ca> wrote:
>>
>>> Ugh! Are you seriously recommending the network data model?
>>
>> He is recommending not inventing the wheel.
>
> No, he is recommending replacing the wheel with a travois. Just because
> a 'solution' exists doesn't mean it is worthwhile.
>
> What he wrote and the link he provided in no way help the original
> poster with his question.

If you'd like to make a career out of telling me what I meant there
should be an opening in the year 2031. Right now the position is taken
and there is a waiting list.

Sybrand is correct in his statement about what I wrote. But if you don't
like hearing it from me, or Sybrand, then read Tom Kyte's books that
discuss the subject. Start with "Expert one-on-one Oracle."
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> Bob Badour wrote:
> > Sybrand Bakker wrote:
> >
> >> On Sat, 12 Aug 2006 17:15:27 GMT, Bob Badour
> >> <bbadour@pei.sympatico.ca> wrote:
> >>
> >>> Ugh! Are you seriously recommending the network data model?
> >>
> >> He is recommending not inventing the wheel.
> >
> > No, he is recommending replacing the wheel with a travois. Just because
> > a 'solution' exists doesn't mean it is worthwhile.
> >
> > What he wrote and the link he provided in no way help the original
> > poster with his question.
>
> If you'd like to make a career out of telling me what I meant there
> should be an opening in the year 2031. Right now the position is taken
> and there is a waiting list.
>
> Sybrand is correct in his statement about what I wrote. But if you don't
> like hearing it from me, or Sybrand, then read Tom Kyte's books that
> discuss the subject. Start with "Expert one-on-one Oracle."
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Personally, I find it quite refreshing when people start telling me
what I meant! I'm not rushing to join any waiting lists though.

Giraffe wrote:
> Hi all,
>
> Please bear with me I've tried to make my question as terse as possible,
> whilst not appearing confusing (hopefully I've not failed in this regard).
>
> I'm trying to store a Chart configuration in an Oracle database
> (although I'm hoping that my choice of database will not lead to a
> solution specific to it, since so far my database is not platform, or
> database specific).
>
> One of these chart entities has two or more axes (one x and at least one
> y) related to it, and each axis relates to zero or more plot entities
> (which store plot information such as plot type, line or columns/bars
> etc). these plot entities relate to a specific item of data that can be
> plotted (and relates to a Unit table).
>
> So far I have
>
> relations-> Chart (1-3) Axis (0-many) Plot (Many-1) DataItem
>
> (I'm assuming I've got this relationship right)
>
> How can I now insert a relationship with a Unit entity so that I can
> ensure that Axis and Plot can refer to any unit in the unit relation *so
> long as its the same Unit*. I'd like to enforce at a relational level a
> limitation so that any number of plots can be configured for a specific
> axis so long as they are all of the same unit. For instance income and
> outgoings can both be related to an axis since they share the same unit
> of currency. But weight and income cannot be plots that relate to one
> axis since there are two units here and an axis can refer to only one unit.
>
> It's easy to enforce this in my application but I was hoping to somehow
> put in some kind of constraint at the database level to stop people
> setting up charts with an axis that allows income, weight, and time to
> all be plotted on the same axis.
>
> Perhaps someone has stored a chart configuration in a database before
> and can offer me some insight.
>
> Many thanks,
>
> Giraffe.

It certainly sounds like the Axis entity has a unit associated with it.
So Axis and Plot both reference the Unit entity. As Bob said, you might
consider the Unit attribute of the Axis as part of its primary key and
the Unit attribute of the Plot as part of its PK with Foreign keys on
both pointing to Unit and a FK from Plot to Axis.

But this seems all very limiting. What happens if someone wants to plot
say growth measurements fro two different studies. Can you plot Yards
and Meters on the same axis? How about Dollars and Yen?

I think you model needs a little more thought.
HTH,
Ed