Re: Using varchar primary keys.

[Misa Simic <misa.simic@xxxxxxxxx>]

Hi Tim,

Debate "natural" vs "surrogate" key last for a too long time - with no official winner...

I think it is more "religional" then real issue...

Advice will be simple: pick approach what best fit your needs, taking into account now and tomorrow (probability of change)...

SQL standard and normal forms are fine with both religions... And it really has very small impact on: speed and storage size (real things) - that is not worth mentioning

I think, I personally could be put to "surrogate" religion - but in described scenario, having tables with just two columns: pk and unique - I would never suggest that, because of simply from practical, common sense, view - it doesn't make sense...

So probably in your scenario you should pick "natural" approach - for described scenarios...

Kind Regards,

Misa

---

From: Tim Uckun
Sent: 01/04/2013 01:36
To: pgsql-general
Subject:  Using varchar primary keys.

Consider the following scenario.

I have a typical tagging structure. There is a table called tags, there is a table called taggings. The taggings table acts as a many to many join table between the taggers and the tags.

The tags table has two fields id and tag. id is a serial data type.

The taggings has a tag_id field along with the context, tagger_id etc.

I don't think there is even one query in this scenario which does not join the taggings table to the tags table so I am wondering if there is any advantage at all of having that id field in the tags table. Why shouldn't I just put the tag itself as the primary key?  The tags are quite short so if pg is smart about it I would guess they would take up less space than an integer in some cases.

I guess the same scenario happens in many of my lookup tables. I have all these tables with just two fields in them.  id field and then some varchar field.  Then every time I want to display that record I join with five other tables so I can look up all the associated lookup tables and display the text value to the user.

Given that you can still set foreign keys and cascade commands to adjust child records either way the tables would be properly normalized so I am wondering what I am gaining by using these serial ID fields.

Is this false economy?