Chris,
> The varchar primary key idea (which I think is probably the best
> solution) is certainly normalized, but it is also certainly inefficient
> disk-wise.
Only if you're real short on RAM. Tiny lookup tables tend to get cached in
the shared buffer cache and stay there. Your only real overhead is if the
application has dozens of ENUMs in a query, causing the number of joins to
exceed the number the plannner can plan well. Otherwise, you're preaching
false optimization.
Overall, I'd say that this is really a waste of time compared to the kind of
things you *could* be doing to make converting from MySQL easier, like
updating and maintaining the database conversion scripts, writing substitutes
for last_insert_id and replace into, or (best of all) writing a detailed
"PostgreSQL for MySQL Users" guide. I personally have converted 3 production
applications from MySQL to PostgreSQL, and encountered two total ENUM columns
in the process.
--
Josh Berkus
Aglio Database Solutions
San Francisco
