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
