Re: Help with SQL constraint

From: <hasta_l3_at_hotmail.com>
Date: 17 Feb 2007 08:13:43 -0800
Message-ID: <1171728823.085040.211170@m58g2000cwm.googlegroups.com>

On 16 fév, 19:07, DA Morgan <damor..._at_psoug.org> wrote:
> hasta..._at_hotmail.com wrote:
> > On 16 fév, 17:24, DA Morgan <damor..._at_psoug.org> wrote:
> >> hasta..._at_hotmail.com wrote:
> >>> On 16 fév, 01:48, DA Morgan <damor..._at_psoug.org> wrote:
> >>>> dean wrote:
> >>>>> On Feb 15, 11:41 am, DA Morgan <damor..._at_psoug.org> wrote:
> >>>>>> Arto Viitanen wrote:
> >>>>>>> dean wrote:
> >>>>>>>> Hello all,
> >>>>>>>> A table T has 2 fields, one (L) holding letters 'Y' and 'N', and one
> >>>>>>>> (X) holding numbers. Is there a (non trigger)constraintsuch that for
> >>>>>>>> L='N' (and only this letter) the numbers must be unique? Records
> >>>>>>>> where L='Y' do not have to be unique.
> >>>>>>>> (I need to join another table to the L='N' group of records, and the
> >>>>>>>> join must be key-preserved).
> >>>> So, essentially, you want to NOT have unique data but convince Oracle,
> >>>> by some hocus-pocus magic trick, that the data is unique.
> >>>> As I presume you intend to put this into production there is no more
> >>>> helpavailable from me. > YOYO. There is one and only one solution ...
> >>>> correctly model your business requirement.
> >>> I dont have the slightest idea of the OP requirements, Daniel,
> >>> but consider this one :
> >>> You are monitoring N crossroads. On each crossroad, there is a
> >>> sensor that detects whether a car is passing through (actually, we
> >>> need four, but well... :-)
> >>> A sensor sends a message to the monitoring application whenever
> >>> a car is seen.
> >>> You have to count and keep an history of the number of cars passing
> >>> through each crossroad within period of times of - say - one hour.
> >>> Traffic is very intense.
> >>> How would you model this ?
> >> You store all hits received even when they are dups.
> >> Then, in your report, or using a materialized view, you formulate an
> >> accurate report based on that data.

> >> All hits received from all sensors are equally valid. It is the job
> >> of reporting to turn it into information.

> > That's indeed the best solution, Daniel.

>


> > However, I stressed that traffic is huge. You have may

> > store 2 or 300 times more information than what the
> > requirements ask for.

>


> > When the history to be kept is huge by itself,

> > then it makes (at least some) sense to accumulate
> > the data at the acquisition level.

[Rearranging]

> Personally I'd keep all of the data ... because with little room
> for doubt ... someone next week or next year will ask for something
> based on what was thrown away.

Granted. But a 300 factor is pretty significant, in some situations.

Often, we cannot reasonnably require 15 Terabytes hard disks when 50 gigs will do nicely :-)

> Perhaps. But lets take another look at the situation. Presuming
> there is a front-end ... what is the longest amount of time that
> is counted for a vehicle to enter and depart the zone? How about
> 24 hours? What if it drives in on Monday and departs three weeks
> later? Is that one event?

> If it is a reasonably finite time ... cache the data real-time
> in something like TimesTen and then only write the final result
> to a results table.

Again a valid solution, that may be badly required if - say - the acquisition rate is very high. But then, the data for the current (or active or open) period is not available in the Oracle database for
reporting. However, that period may be the most interesting one for the user.

Summarizing so far, we have the following high-level designs

Keep all data is in the (Oracle) database. Most open and flexible. Prefered if there are no further constraints.
Stores accumulated data as well as the active period in the database. Fragile against requirement changes. May be required by space constraints.
Accumulate data at the acquisition level, and stores only consolidated data in the database. Fragile against requirement changes. Introduces latency. Deviates most from the "ideal" model A. . May be required by time constraints.

None of these models strikes me as distasteful. All are IMO valid conpromises given different sets of constraints.

Now, assuming up-to-date data is needed for reporting, we may focus on B. This can be implemented as

A table with for each crossroad with both past (closed) periods and the current (open) period. The table will be queried by the reporting engine. Here we find ourselves in a situation similar to the OP one. Easy implementation. Integrity constraints not directly testable by Oracle.
As pointed out here, a table with history and another with data being accumulated. A view presents the union of both to the reporting engine. Slightly more difficult to implement and maintain than 1. May be faster or slower to update. Integrity constraints directly testable by Oracle.
?

My point here is to show that model B. is not intrinsically bad. Moreover, whether to implement it with 1. or 2. or ? is a low level design decision, which is not driven by the user requirements.

Have a nice day, Daniel

Raoul

Received on Sat Feb 17 2007 - 10:13:43 CST