Hi
My case sensitive question has morphed so I have separated this out.
I wonder about the wisdom of dumping unique complex ids in favour of
just using the next number plus a set of indices.
No problem for log or transaction files as they should never be
altered anyway.
However in the case of say a debtors master and delivery address files
I can see a lot of problems.
Debtor key company*debtor id 1*29 in my case the 9 is a mod11 check
digit based on the combination of a fixed length company and fixed
length debtor id for the purposes of calculation only.
Debtor's delivery addresses 1*29*0 .....999
It is essential that these items be locked and updated , never
duplicated. It seems that one has a deal more handling if one has to
index search every time. In fact it can turn into a duplicated record
very easily.
Although I can see that flagging the record as closed and making a new
one could bypass the need for a separate audit change file.
Also I have always maintained that indices are simply look up devices
and can always be rebuilt from the master file. In the numeric id
case this seems to go out of the window as duplicates will be
replicated.
Any thoughts welcome
Peter McMurray

On 2011-04-06 19:01:50 -0400, Excalibur21 <pgmcmurray (AT) gmail (DOT) com> said:
We all accept that MV data integrity is the job of the application
programmer. Outside of triggers, there are database-level mechanisms
to enforce any data constraints, such as unique index keys. However,
we've all been doing such integrity management for decades, though I
agree that with indexes a little more, or maybe just a little
different, code is required.

Buggy code can create all kinds of problems. In an index based system,
maybe it's a duplicate record. In a system where the ID is the primary
key, it's a record that is lost/overwritten. Neither is desirable, but
one may be less catastrophic.

--
Kevin Powick

On 2011-04-06 20:59:40 -0400, Kevin Powick <nospam (AT) spamless (DOT) com> said:

That should have read, "... there are *no* database-level mechanisms...."

--
Kevin Powick

On Apr 6, 9:37*pm, Kevin Powick <nos... (AT) spamless (DOT) com> wrote:
Actually should have read "no MV Database level mechanisms...."
That's what gave Bad Odor and others the ammunition to sink us at the
Universities and since their grads soon were running the IT world it
soon sunk us in the rest of the world.
BobJ

On 2011-04-07 04:36:54 -0400, Robert Joslyn <bobjoslynalt (AT) gmail (DOT) com> said:

I kind of figured that the "MV" part was a given, considering the forum
and all.

With the growing popularity of NoSQL databases such as CouchDB,
MongoDB, etc., I wonder if that school of though is changing? Many of
those NoSQL databases attribute, to a degree, their scalability to the
fact that data integrity is not built into the database and, just like
MV, must be maintained at the application level.

Actually, I'm aware of some large, high-throughput systems built on
SQL/RDBMS that do not use any database level integrity checks because
it is too much of a performance penalty.

--
Kevin Powick

On Apr 7, 10:21*pm, Kevin Powick <nos... (AT) spamless (DOT) com> wrote:
Hi
The latest move to provide a standard approved JavaScript linked
relational database should be very interesting as to exactly what data
verification is enforced.
I find the flexibility (agility in newspeak) a major benefit in
development. I have very sad memories of having to drop the entire
database just to add a new field in Informix. Although I understand
that Oracle and others have improved in this regard, it is only a few
years back that I remember staff at a client tearing their hair out as
the payroll developer lost their payroll entries (every 6minutes
accounted for) for the nth time as he fiddled. I never did find out
whether this was a problem of the database or merely an idiot
developer - said developer was taken on a world trip as the resident
expert and described as a genius by the owner!
The simple fact is that Pick is flexible enough to allow one to impose
a schema that is much more powerful. I have always considered file
based schemas to be inappropriate for application development and
regard attempts to use the Pick dictionaries as such to be totally
misguided.
Peter McMurray

On Apr 6, 7:01*pm, Excalibur21 <pgmcmur... (AT) gmail (DOT) com> wrote:

I'm not sure what you're root conversation was but if you're trying
to use check digits in an ID to represent a complex data lookup then
you're on the right track. You still need to perform additional
granular checking once you've found possible matches, though. An MD5
match would be a 100% confidence match, but that requires user input
to exactly match the data on file. It either works or doesn't, there
is no partial match confidence. Many of the address matching and de-
duping products out there take 3 or more chars from each field (user-
configurable based on site testing) and build a single hash from all
of the fields assigned to the 'key builder'. That hash can be stored
in the address/customer/debtor file and then sorted, selected, etc.
The tricky part, though, is how to perform "fuzzy logic" on fields
that aren't 100% matching. SOUNDEX is usable in some cases but there
are instances like nicknames that can apply due to legal v/s casual
spelling on forms. You would need a bunch of supplemental databases to
support common misspellings and alternate spellings for words and
phrases. I know that doesn't help or provide anything useful but I
just did some research on this kind of problem regarding address
validation and account matching myself. We want to automate some
processes and the fact is there is no way to avoid duplicates if any
of the parties involved do not care about data quality. A customer/
client driven key will be more successful compared to a system driven
one. I think we can get pretty close to a blind-check solution by
using a for-fee address matching/lookup solution that produces a
complex hash based on name, address, e-mail and phone number. The
fuzzy lookup engine can be used in conjunction with that stored hash
and a quick check against a specific field or two to increase
confidence for such a small subset of matches.

GlenB