Using varchar primary keys.

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Using varchar primary keys.
Date: 2013-03-31 23:35:47
Message-ID: CAGuHJrNUd-34fq0Ee-P6=-DDBa6T1ROthchsj6fuTbuwsGuVVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pierre 2013-04-01 00:46:11 my-post-engine, let's make MySQL speak to PostgreSQL
Previous Message Juan Pablo Cook 2013-03-31 22:49:06 Re: Fwd: JDBC Array double precision [] error