Do's and Don'ts of SQL

[Dan Gidman]

Well guys this may be the wrong place but an earlier post by an
"expert" about how a table was poorly designed has piqued my interest.
The question is this. What are the do's and don'ts of sql development?

Please list what you consider to be good and bad practices in general
and/or specific or list links to resources that would be considered
under this topic.

When I consider best practices myself of course there is always the
standards of 3nf and such but what about when it comes to standard
table structures and field sizes for say a Name (first, last, etc) and
contacts table. What about naming conventions and common entities and
structures.

It seems to me that every SQL Developer that enters this field starts
out with the basics of how the technology works and a little of the
practices. Yet from there I see many different directions that they
go.

To start this conversation out let me propose some things that I have
questions about. Feel free to expand and add to this list.

1. What would you consider the standard/best way to represent a
gender.

2. Field Size for Names and address First/Last Etc.

3. Include or don't include City/State in an address/contact list.

4. Structure of a Generic Contact list.

5. Practices for dealing with pictures and documents as well as
related table structures.

6. Level of normalization to aim for. (My minimum is 3 but I
generally hit Boyce Codd myself)

7. What datatypes to avoid and why.

Two requests.
1. Keep it polite.
2. Be constructive and complete.

[Dan Gidman]

Just as I was posting this I saw a link to a fine articles on the best
practices. Here it is

http://vyaskn.tripod.com/coding_conventions.htm

[--CELKO--]

Want to wait a bit for my next book, SQL PROGRAMMING STYLE to come out?
1. What would you consider the standard/best way to represent a
gender. Use the ISO codes, since they are standards, of course.

2. Field [sic] Size for Names and address First/Last Etc. Use the USPS
model, which is based on a five line, 3.5 inch label with 10 pitch type
on it.

3. Include or don't include City/State in an address/contact list.
Include it since you need it to mail anything.

4. Structure of a Generic Contact list. Unh? That is not a question.
5. Practices for dealing with pictures and documents as well as
related table structures. Use tools intended for them. There are
several good textbases. I have not worked with graphic search tools.

6. Level of normalization to aim for. (My minimum is 3 but I
generally hit Boyce Codd myself) if you use an ORM model instead of
ER, you will get to 5NF.

7. What datatypes to avoid and why. Anything proprietary because it
will not port, cannot be guaranteed to be consistent from one release
to the next, etc.

[Dan Gidman]

Well CELKO said me a copy to review and I'll be happy to read it :)
However many people's first target for questions and advise is the
internet and usegroups like this.
This is a resource that we need to continue to support and add to.
Since it is a resouce for all of us. Not just those with a specific
book on their shelf. (not to down play your book which I actually may
go get or look at after it's published) Good luck with the book in the
meantime.

[Adam]

I had a comment on the normalization. We have a reporting database that is
updated nightly. It runs on a little server for only management accesses.
If I normalized this database, reports would run 10 minutes. I normally
build non-normalized tables for the reports and they run in a couple of
seconds. Therefore, my point is normalization really depends on the desired
application.
"Dan Gidman" <da*******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Well guys this may be the wrong place but an earlier post by an
"expert" about how a table was poorly designed has piqued my interest.
The question is this. What are the do's and don'ts of sql development?

Please list what you consider to be good and bad practices in general
and/or specific or list links to resources that would be considered
under this topic.

When I consider best practices myself of course there is always the
standards of 3nf and such but what about when it comes to standard
table structures and field sizes for say a Name (first, last, etc) and
contacts table. What about naming conventions and common entities and
structures.

It seems to me that every SQL Developer that enters this field starts
out with the basics of how the technology works and a little of the
practices. Yet from there I see many different directions that they
go.

To start this conversation out let me propose some things that I have
questions about. Feel free to expand and add to this list.

1. What would you consider the standard/best way to represent a
gender.

2. Field Size for Names and address First/Last Etc.

3. Include or don't include City/State in an address/contact list.

4. Structure of a Generic Contact list.

5. Practices for dealing with pictures and documents as well as
related table structures.

6. Level of normalization to aim for. (My minimum is 3 but I
generally hit Boyce Codd myself)

7. What datatypes to avoid and why.

Two requests.
1. Keep it polite.
2. Be constructive and complete.

[--CELKO--]

>> I normally build non-normalized tables for the reports .. <<

This is a "Data Warehouse" versus "OLTP database"; you can do this only
because the warehouse is loaded from a normalized production database
and then is never updated while in use. A data warehouse is a very
different game. Even teh quereis are different.