Re: Finding sequential records

At 09:50 PM 9/29/2008, Richard Broersma wrote:
>On Mon, Sep 29, 2008 at 7:48 PM, Steve Midgley <science@misuse.org> 
>wrote:
>
> > In my specific case it turns out I only had duplicates, but there 
> could have
> > been n-plicates, so your code is still correct for my use-case 
> (though I
> > didn't say that in my OP).
>
>Ya there are a lot of neat queries that you can construct.  If you
>have a good background in math and set theory (which I don't have) you
>can develop all sorts of powerful analysis queries.
>
>On a side note, I thought that I should mention that unwanted
>duplicates are an example where some ~have gotten bitten~ with a
>purely surrogate key approach.  To make matter worse, is when some
>users update part of one duplicate and another updates a different
>duplicated on a another field(s).  Then once the designer discovers
>the duplicate problem, she/he has to figure out some way of merging
>these non-exact duplicates.  So even if the designer has no intention
>of implementing natural primary/foreign keys, he/she will still
>benefit from a natural key consideration in that a strategy can be
>designed to prevent getting bitten by duplicated data.
>
>I only mention this because db designers get bitten by this all the
>time.  Well at least the ones that subscribe to www.utteraccess.com
>get bitten.  From what I've seen not one day has gone by without
>someone posting a question to this site about how to both find and
>remove all but one of the duplicates.

Truly. I have worked with some school districts around the US and this 
duplicate record problem is more than theoretical. Some of the 
gnarliest, dirtiest, n-plicate data I've ever seen comes out of the US 
public education system.

More generally where I have seen a need for natural keys, I've always 
taken the "best of both worlds" approach. So I always stick an 
integer/serial PK into any table - why not - they're cheap and 
sometimes are handy. And then for tables along the lines of your 
description, I add a compound unique index which serves the business 
rule of "no dupes along these lines."

Am I following your point? Any reason why using serial PK's with 
"compound natural unique indices" is better/worse than just using 
natural PK's?

Steve