Re: Primary keys in a single column table and text vs varchar

On Mon, Jan 31, 2011 at 7:54 PM, matty jones <urlugal(at)gmail(dot)com> wrote:
> I am designing a table that will hold a list of unique names and be
> referenced by several other tables.  There will only be one column in the
> table (name), should I still create a separate primary key for that column
> or can I use the name column as my key.  I have also been reading a lot
> about the differences between varchar and text and was wondering if there is
> a downside to using text.  The column I want to use text on (notes) will
> hold a string that could be any length, I can use varchar(255) but I am
> worried about the text being truncated or of the queries giving errors when
> I try to output the text.  I understand there is a mathematical limit to how
> much can be stored in a single entry but I wasn't sure of any downsides to
> just defaulting to text when possible.
> Thanks,
> Matt

It may help you to read pages on natural key versus surrogate key
discussions, like this page:
http://www.agiledata.org/essays/keys.html
To summarize briefly, using the name column as the primary key makes
your database easier to understand, but if you need to change the
table in the future and add or remove additional unique constraints,
changing your database is a lot of work. Using a separate column for
the primary key gives you the converse situation, so your database
layout is less intuitive, but changing the tables in the future
becomes much easier. PostgreSQL will handle either type of primary
key just fine. In my limited experience a separate key usually is a
better choice, but I've only been wrestling with databases for a few
years.

In terms of text versus character varying, I don't think the
differences are significant. I would use character varying(500) or
(1000), so you have plenty of room for big names but you can't
accidentally have a 10MB entry in the table.

Good luck,
Mike Swierczek