Code Tables: varchar or serial pkey? - Mailing list pgsql-general

From Peter Fein
Subject Code Tables: varchar or serial pkey?
Date
Msg-id 43273D01.2050604@pobox.com
Whole thread Raw
List 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@pobox.com                 773-575-0694

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

pgsql-general by date:

Previous
From: Russ Brown
Date:
Subject: Re: Replication
Next
From: Federico Kereki
Date:
Subject: Question about PHP and PostgreSQL