Code Tables: varchar or serial pkey?

From: Peter Fein <pfein(at)pobox(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Code Tables: varchar or serial pkey?
Date: 2005-09-13 20:56:33
Message-ID: 43273D01.2050604@pobox.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Any recommendation on the use of serials vs. varchars as primary keys
for code tables? By code table, I mean a small table containing a few
application values that is foreign keyed by a table containing user
data. I currently have something like:

Table "public.education_codes"
Column | Type | Modifiers | Description
----------------+-----------------------+-----------+-----------------------
education | smallint | not null |
display_string | character varying(50) | not null | display value
short_name | character varying(15) | not null | alias for use in code
Indexes:
"education_codes_pkey" PRIMARY KEY, btree (education)
"education_codes_short_name_key" UNIQUE, btree (short_name)

Table "public.my_table"
Column | Type |
Modifiers
------------------------+-----------------------------+-----------------
education | smallint |
some_other_cols | text |
Indexes:
"fki_education" btree (education)
Foreign-key constraints:
education_codes(education) ON UPDATE CASCADE ON DELETE SET NULL

petetest=# select * from education_codes;
education | display_string | short_name
-----------+-----------------------+--------------
1 | Associate's Degree | ASSOCIATE
2 | Bachelor's Degree | BACHELORS
3 | Doctorate | DOCTORATE
4 | High School or G.E.D. | HIGHSCHOOL
6 | Masters | MASTERS
7 | Some College | SOMECOLLEGE
9 | M.B.A. | MBA
10 | M.F.A. | MFA
12 | Other | OTHER
8 | J.D. | JD
5 | Less than High School | NOHIGHSCHOOL
11 | Vocational Degree | VOCATIONAL
(12 rows)

I use short_name in my application - it's easier to debug/inspect/code
(as there's a human-readable string instead of an integer) and convert
to a smallint (education) when inserting into my_table.

Now it seems that short_name could just as easily be used as the pkey on
education_codes. I went the serial initially route b/c I thought it
would be faster and to reduce space used by my_table, which may have
100's of millions of rows down the road.

Are these good reasons? I'm thinking about switching to using the
varchar col as the pkey/fkey just to make my coding easier. How do
people do this sort of thing? TIA.

--Pete

--
Peter Fein pfein(at)pobox(dot)com 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman

Browse pgsql-general by date

  From Date Subject
Next Message Federico Kereki 2005-09-13 21:05:04 Question about PHP and PostgreSQL
Previous Message Russ Brown 2005-09-13 20:51:18 Re: Replication