Re: Storing computed values

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