Richard Broersma wrote:
On Mon, Apr 21, 2008 at 12:24 PM, Colin Wetherbee
<cww@xxxxxxxxxxxxxxxx> 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
