[postgis-users] Dilemma: is gid really useful in a geoDB?

[Marc Compte]

Hi,

Ermm ... wasn't one of Codd's rules "Each and every datum (atomic value) 
in a relational data base is guaranteed to be logically accessible by 
resorting to a combination of table name, primary key value and column 
name."?

I find it surprising to see that "there's no need for a gid, from the 
database point of view" and that primary keys are only needed because 
"most applications want some way of identifying specific rows", as the 
very core definition of the relational model clearly identifies this as 
a crucial part of the model. Wanting to identify specific rows is not an 
application oddity, it's part of the very definition of the model.

Every table needs to have a key, not only to conform with the relational 
model's theory, but for pragmatical purposes as well. Even (or 
specially) from the database point of view, no key means there is no way 
to update a unique field, there is no way to extract the information 
about one individual record, there is no way to cross-reference tables 
... in short, no key means something like there is no relational 
database, only a flat list of data ... and PostgreSQL, even though it 
incorportes OO elements, has still a big part of relational, right?
 
Whether you have to be using that particular field (gid) or not depends 
on your data. I don't think naming your key field "gid" is defined in 
any standard (it shouldn't be anyway). As other people said, shp2pgsql 
automatically generates this one for you, but I assume that is just to 
make your life easier and make sure there is at least one way to 
uniquely identify records.

The only important thing to do should be to make sure that there's at 
least 1 field with values that can be used to uniquely identify any of 
the records of your table. If you are sure there's another field (or 
combination of fields) that serves the purpose right, you wouldn't 
actually need to have that particular gid field. You should be able to 
delete it and still be able to use on that table all of the features of 
the database and any other surrounding application by using your own 
self-made primary key.

If an application has a problem with any of this (multiple field keys, 
using a different name for the key field other than gid, ...) then you 
could probably say it is not well designed and you might want to switch 
to something else.

Marc

En/na Markus Schaber ha escrit:
> Hi, Antonello,
>
> Antonello <antonello.monetsen at gmail.com> wrote:
>
>   
>> I have a dilemma: is gid really useful in a geoDB?
>> I am building a geodb for several harbor. When I create a layer(table 
>> with geometry) in postgis, I need a gid like a primary key, or I can use 
>> a different one? Can I  use several field of from the table like a 
>> primary key?
>> I am not new in the gis, but why in almost all geoDB I saw there is a 
>> gid?! Is it really necessary? Do it come from shapefile standards?
>>     
>
> Technically, there's no need for a gid, from the database point of
> view, as SQL deals with sets of rows.
>
> However, most applications want some way of identifying specific rows,
> especially when updating / editing data.
>
> GID is generated by shp2pgsql.
>
>   
>> For example QuantumGIS doesn't work if the layer has only one primary 
>> key, there are other application that don't work like qGis?
>>     
>
> QuantumGIS has the additional oddity that the primary key has to be of
> type int4 - int8 or serial (which is optimal for primary keys) is not
> accepted, AFAIK.
>
> For read-only access, you can try MezoGIS which makes very little
> assumptions about your database, and you can specify layers via
> arbitrary queries.
>
>
> Regards,
> Markus
>
>