I have read that using OIDs as a primary key in a postgreSQL/PostGIS db is poor practice because there are instances where these can be reset. Sounds logical, but then what is a suitable alternative? I believe there is an option to use a "Universal Unique Identifer" UUID, but the large text and number value that spits out is horrible.
Just a bit more background to my situation. I have all of my spatial tables created with a field called "gid" which is the primary key for that table and unique only to that table. I have an issue now because I want to relate my spatial tables (all with a "gid" field starting at 1 and incrementing) to one large table with the related information. Obviously for my relationship to work all of my spatial features need a unique identifier which differentiates them from one another.
EDITED Added this image as per Peters' comment. Peter this is the idea I have in my head, it may not be the best way to go about it or it might not even be good db design. I am interested in what you think.
Any tips?
I believe there is an option to use a "Universal Unique Identifer" UUID, but the large text and number value that spits out is horrible.
Why does it matter what the unique ID looks like? – nmtoken Nov 2 '17 at 7:55