database - To improve SQL-queries in DDL

Question

Improvements done

1. nvarchar(5000) -> nvarchar(4000) BUT no nvarchar in PostgreSQL => TEXT
2. memory limits to some variables
3. the syntax slightly changed to more readable
4. dashes to underscores
5. Magnus' improvements

I am following my plan for my first database project.

I would like to know any weaknesses in the queries and in the relational table.

SQL-queries in DDL

CREATE TABLE answers 
(
    question_id INTEGER FOREIGN KEY REFERENCES questions(user_id)
                        PRIMARY KEY 
                        CHECK (user_id>0), 
    answer TEXT NOT NULL      -- answer must have text
);

CREATE TABLE questions 
(
    user_id INTEGER FOREIGN KEY 
                    REFERENCES user_info(user_id) 
                    PRIMARY KEY 
                    CHECK (user_id>0), 
    question_id INTEGER FOREIGN KEY REFERENCES tags(question_id) 
                        NOT NULL 
                        CHECK (user_id>0)
                        SERIAL, 
    body TEXT NOT NULL,                    -- question must have body 
    title VARCHAR(60) NOT NULL,            -- no empty title
    moderator_removal BOOLEAN NOT NULL,    -- by default false
    sent_time TIMESTAMP NOT NULL
);

CREATE TABLE tags 
(
    question_id INTEGER FOREIGN KEY REFERENCES questions(question_id) 
                        CHECK (user_id>0), 
    tag VARCHAR(20) NOT NULL,
    CONSTRAINT no_duplicate_tag UNIQUE (question_id,tag)
)


CREATE TABLE user_infos 
(
    user_id INTEGER FOREIGN KEY REFERENCES questions(user_id) 
                    PRIMARY KEY 
                    CHECK (user_id>0)
                    SERIAL
                    UNIQUE, 
    username VARCHAR(25),
    email VARCHAR(320) NOT NULL       -- maximun possible
                       UNIQUE,
    password_sha512 INTEGER NOT NULL,
    is_moderator BOOLEAN NOT NULL,
    is_Login BOOLEAN NOT NULL,
    has_been_sent_a_moderator_message BOOLEAN NOT NULL
);



-- to have default values

ALTER TABLE questions ALTER COLUMN moderator_removal SET DEFAULT FALSE

ALTER TABLE user_info ALTER COLUMN is_moderator SET DEFAULT FALSE
ALTER TABLE user_info ALTER COLUMN is_login SET DEFAULT FALSE
ALTER TABLE user_info ALTER COLUMN has_been_sent_a_moderator_message SET DEFAULT FALSE


-- to have default values

ALTER TABLE questions ALTER COLUMN moderator_removal SET DEFAULT FALSE

ALTER TABLE user_info ALTER COLUMN is_moderator SET DEFAULT FALSE
ALTER TABLE user_info ALTER COLUMN is_login SET DEFAULT FALSE
ALTER TABLE user_info ALTER COLUMN has_been_sent_a_moderator_message SET DEFAULT FALSE

Relational Table

alt text http://files.getdropbox.com/u/175564/db/db777.png

What would you improve in the DDL queries?

Answer 1

When you use varchar(4000) and so, is 4000 actually a conceptual maximum of how long the string can be there? Or did you just pick something "big enough for everything"? If the second, just use the text datatype. It will be just as fast (actually, a tiny bit faster, but you will not likely be able to measure that).

sent_time looks like it should be a timestamptz. Don't store date/time in a varchar.

auto_increment is not in postgres, use a serial column.

You have a circular reference between Tags and Questions, which I'm sure you didn't intend. And your check constraint on Questions.question_id appears checks user_id - too much copy/paste I bet.

Finally, don't use mixed case identifiers. Do everything lowercase, so you don't have to quote them. For instance, use lowercase for column and table names.