Notes in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th
"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message
news:73e20c6c.0410081937.7913e05e_at_posting.google.com...
>
> > How does the client manage to enter thousands
> > of child rows, when the error is caused by a
> > mis-type ?
> >
>
> Because users are not perfect typists and auditors
> only do their work every once in a while.
This doesn't make your original suggestion any
more likely to be realistic. Manual data entry of child
rows normally requires at least an initial cross-check
against the existence of a parent. Your "thousands"
of rows would probably require a typist to make the
same typing error thousands (or at least hundreds) of
times in a rows without noticing. And if the data entry
was a consequence of acquiring the real value from a
real object this seems an unlikely chain of events.
> Expecting a user to always enter the correct data
> upfront and presume that data is correct enough to form
> the base of a hierarchy or series of relationships
> is like expecting ice cream to not melt down your
> fingers: looks good and tastes good, but it WILL
> melt.
Ice cream melt ? Not in the UK ;)
> > Deferrable Constraints should deal with this.
> > And if not, then may not be a huge problem
> > to produce a generic copy/delete/insert generator
> > that walks the constraints to produce a program
> > that does the job on auto-pilot.
>
> I'd rather never have to do it. Particularly
> when I may have very complex relationships
> hanging off the PK.
>
There are no such things as complex relationships -
there are only collections of very simple PK/FK links.
>
> > What about all those wonderful big systems
> > that suddenly realise that they can't do partitioning
> > with local primary key indexes because the
> > "natural" partitioning column is not part of the primary
> > key - and the users don't get any partition elimination
> > in their queries because the only possible elimination on
> > a meaningless key which they don't know ?
>
> Are you saying that he only effective way of partitioning
> a table is through the PK? What about all those systems
> out there partitioned on a rolling date which has nothing
> to do with the PK?
It may be a coincidence, but all the systems that I
have seen that want to partition on a "rolling date"
want to do so because they want to drop old partitions
and load new data efficiently. If they were able to just
drop the data "because the date is in the past" then, and
this is perhaps the coincidence, the date ALWAYS
turned out to be a part of the primary key.
> And since when is partitioning dependent on PKs or vice-versa?
>
Clarification: I was thinking only of Oracle systems
and the Oracle implementation of partition independence
which requires a guarantee that (and it isn't worded this
way in the manuals) the partition that a row belongs in
should be uniquely defined by the value of any primary
or unique key that has been defined on the table.
> In fact, what I'm seeing more and more is users requesting
> systems that let them enter the data ANYWAY, and then allow
> them to go back and fix things later. I call it "fuzzy
> data entry". It is the bread and butter of any new designs
> (particularly when they are re-hashes of less stringent
> data entry products like Lotus Notes and such) and is now
> in just about any new system I get asked to do. We now
> have the hardware to make these things fly. But natural keys
> are an absolute no-no in such a situation.
If you think the hardware can make rubbish fly, then
the hardware should be able to deal with a large volume
of cascading updates across primary/foreign keys. But
you've discounted that as not viable. If you're happy
to write code to handle rubbish that needs to be cleaned
up, then it's a bit unreasonable to say that you'd rather
not have to do it. (Feel free to replace "rubbish" with
"fuzzy data" in the above).
Received on Sat Oct 09 2004 - 07:07:22 CDT
