To improve SQL-queries in DDL

views:

188

answers:

To improve SQL-queries in DDL

+2 A:

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.

Magnus Hagander 2009-07-29 05:43:31

*Finally, don't use mixed case identifiers. Do everything lowercase, so you don't have to quote them.* --- Do you mean I should change the commands such as `CREATE TABLE`, `USER_ID` and `NOT NULL` to lowercase?

Masi 2009-07-29 10:36:30

The following thread is based on your last paragraph: http://stackoverflow.com/questions/1199868/to-understand-a-sentence-about-the-case-in-sql-ddl-queries

Masi 2009-07-29 12:59:54

@Magnus: In which systems do you need *to quote [column and table names]* when you use Uppercase?

Masi 2009-07-29 13:09:40

I removed the primary key form tags(question_id). - Ken says that if you use the primary key in the table too. It allows one question have only one tag at the thread http://stackoverflow.com/questions/1196873/to-prevent-the-use-of-duplicate-tags-in-a-database/1196996#1196996

Masi 2009-07-29 14:28:02

No need to lowercase the CREATE TABLE and NOT NULL parts. But lowercase USER_ID. My experience is that most databases need quoting in cases like this, and the rules for how they fold things are different if you don't quote it. Sticking to just lowercase (or just uppercase - that's mainly a matter of preference) and then never quoting them is a good way to just escape having to deal with it.

Magnus Hagander 2009-07-30 09:16:23

ansaurus

tags:

views:

answers:

To improve SQL-queries in DDL

related questions