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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate