Re: hi all

On Tue, Feb 17, 2009 at 04:40:58PM +0000, Sam Mason wrote:
> > user_name varchar(50) NOT NULL,
>
> As a general design question; should user_name have a UNIQUE
> constraint on it? i.e.
>
> user_name VARCHAR(50) NOT NULL UNIQUE,

Yes, it's good to have a UNIQUE constraint, but not this one. To have
a sane one, it needs further constraints, and in 8.4, case-insensitive
text (citext) type. Here's one that is reasonably sane until citext
is available.

user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR.

then later:

CREATE UNIQUE INDEX unique_user_name_your_table
ON your_table(LOWER(TRIM(user_name)))

You might also require that whitespace be treated in some consistent
way, one example of which is simply forbidding whitespace in user_name
at all. This you can do via CHECK constraints or a DOMAIN.

> creator INT REFERENCES users (user_id),
>
> > date_created timestamp NOT NULL default to_timestamp('0000-00-00 00:00:00','YYYY-MM-DD HH24:MI:SS'),
>
> What's this strange 0000-00-00 date you speak of? As far as I know
> it's not valid; dates go from 1BC to 1AD without a zero in the middle.
> Shouldn't you just remove the NOT NULL check or maybe '-infinity' would
> be better.

Either require a created_date and make the default
sane--CURRENT_TIMESTAMP, e.g.--or don't require one, but making a
nonsense date is Bad(TM).

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate