a few further comments:
On Tue, Feb 17, 2009 at 06:54:53PM +0530, Ashish Karalkar wrote:
> CREATE TABLE users (
> user_id serial NOT NULL ,
It's common to combine this with the PRIMARY KEY constraint from below
to be:
user_id SERIAL PRIMARY KEY,
the NOT NULL check is implicit in this and thus redundant.
> 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,
> secret_question varchar(255),
> secret_answer varchar(255),
as pointed out, these look like they should probably be of TEXT type.
> creator int,
I'd combine this with the FOREIGN KEY constraint from below as well:
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.
date_created TIMESTAMP,
or
date_created TIMESTAMP NOT NULL DEFAULT '-infinity',
or should it really be
date_created TIMESTAMP NOT NULL DEFAULT now(),
> voided smallint NOT NULL default '0',
Is this really an INT, or should it be a BOOL:
voided BOOL NOT NULL DEFAULT FALSE,
> date_voided timestamp,
I tend to have these as "end dates" defaulting to 'infinity' as it's
easier to do checks on them then:
date_voided TIMESTAMP NOT NULL DEFAULT 'infinity',
hope that helps!
--
Sam http://samason.me.uk/