Re: Varchar pkey instead of integer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Robins Tharakan wrote:
Hi,

I am currently designing a database and wanted to know something that may
sound trivial, but I thought its still good to confirm before dumping
millions of rows in it.

The design requires a few master tables with very limited rows, for e.g.
currency_denomination table could at the max have a few records like million
/ billion / crore (used in india) / lacs (india specific) and so on.

Now what I wanted to ask was whether its any different to have the
primary-keys in such master tables as text/varchar rather than integer ?

As far as I know it's just slower to compare (ie for fkey checks, index lookups, etc) and uses more storage. However, if you're only using the other table to limit possible values in a field rather than storing other information and you can avoid doing a join / index lookup by storing the string directly in the master table then that might well be worth it. It's a tradeoff between the storage cost (seq scan speed, index size, etc) of using the text values directly vs the savings made by avoiding having to constantly hit a lookup table.

I have several places in the database I'm presently working on where I store meaningful integers directly in a "main" table and reference a single-field table as a foreign key just to limit acceptable values. It works very well, though it's only suitable in limited situations.

One of the places I'm doing that is for in-database postcode validation. My current app only needs to validate Australian post codes (as per the spec) and other post/zip codes are just stored in the address text. I store the integer representation of the post code directly in address records but use a foreign key to the single-field "aust_post_code" table to enforce the use of only valid postcodes. There's an ON DELETE SET NULL cascade on the fkey because for this app's purpose a postcode that's no longer accepted by the postal service is bad data.

This means that the postcode list can't be updated by a TRUNCATE and repopulate. No big deal; I prefer to do a compare between the current database contents and the latest postcode data and insert/delete as appropriate anyway; especially as the app needs to be able to record and flag tentative entries for postcodes that the user *insists* exist but the latest (possibly even weeks old) australia post data says do not.

You could reasonably do the same sort of thing with a text postcode if your app had to care about non-numeric postal codes.

It's nice being able to work on something that doesn't have to handle pedal-post in some awful corner of the earth where they identify postal regions by coloured tags. OK, not really, but sometimes addressing seems almost that bad.

i.e. Can I use a character varying(10) and use the text 'million' /
'billion' instead of a serial / integer type ?

If you're looking at a small set of possible values an enumeration *might* be an option. Be aware that they're painful and slow to change later, though, especially when used in foreign keys, views, etc.

I certainly wouldn't use one for your currency denomination table, which is likely to see values added to it over time.

p.s.: I am not as much concerned with the size that it'd take on the data
tables, as much as the fact that the select / insert performances shouldn't
suffer. However, if that increase in size (per data record) may make a
considerable impact on the performance, I would certainly want to take that
into account during design phase.

I suspect it's just another tradeoff - table size increase (and thus scan performance cost) from storing the text vs avoiding the need to access the lookup table for most operations.

--
Craig Ringer


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux