From: | Colin Wetherbee <cww(at)denterprises(dot)org> |
---|---|
To: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing computed values |
Date: | 2008-04-21 19:47:55 |
Message-ID: | 480CEF6B.4090000@denterprises.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Richard Broersma wrote:
> On Mon, Apr 21, 2008 at 12:24 PM, Colin Wetherbee
> <cww(at)denterprises(dot)org> wrote:
>
>> Right, but the problem I see is that my locations are not actually
>> stored in foo. Since many rows of foo can reference the same
>> location, the locations are stored in a separate table and, in
>> fact, are referenced by foreign keys (SERIAL/INTEGER types)
>> already.
>
> I see, I haven't used PostGIS yet, so I am not sure how it is used.
> However, I am curious about the advantages or the intent of having
> duplicate points in a table that requires the surrogate ID in order
> to be distinguished these duplicate point. I only ask since I am not
> familiar with postGIS (perhaps this is the way it is intended to be
> used.) To me this seems to be a bit de-normalized, unless there is
> something that differentiates or somehow makes these points unique
> these points.
In the example we're tossing around, these PostGIS points could be
considered to be integers or anything else. I just mentioned that they
were PostGIS points because then it's easier to visualize a procedure
that "connects" them.
The surrogate key exists because that's what users see, and it's a cheap
way for me to validate user input. For example, if a user wants to
connect point_id 123 and point_id 456, that's great, but if a user is
connecting arbitrary longitudes and latitudes to others, that's not so
great. It wouldn't destroy anything, but it's not really how this
application is intended to be used.
OTOH, I could keep the point_id in the points table and just not use it
as the surrogate key for the main table anymore.
> If you constrained your data to only allowed unique points in your
> points table, your problem would be solved in regards to caching,
> since you could simple use natural foreign keys to get the
> connections data that you want.
Indeed.
I'll have to ponder natural foreign keys for a while before I make a
decision on this.
Thank you very much for your input.
Colin
From | Date | Subject | |
---|---|---|---|
Next Message | Kerri Reno | 2008-04-21 21:22:52 | create temp in function |
Previous Message | Richard Broersma | 2008-04-21 19:38:36 | Re: Storing computed values |