From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Finding sequential records |
Date: | 2008-09-30 06:05:32 |
Message-ID: | 20080930060600.02C8C37BFC8@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
At 09:50 PM 9/29/2008, Richard Broersma wrote:
>On Mon, Sep 29, 2008 at 7:48 PM, Steve Midgley <science(at)misuse(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | az | 2008-09-30 08:32:51 | optimizing a query over tree-like structure |
Previous Message | Richard Broersma | 2008-09-30 04:50:14 | Re: Finding sequential records |