Database Design Consideration

[HarryZheng]

* Data logical and physical design
* Frequent select distinct or union serves as a warning that design may have issue
* Normalize database for OLTP
* Consider denormalization to reduce joins
* Be careful about denormalization, use as last resort
* nulls in fixed length column?
* One large database v.s. several smaller databases
* Partitioning large tables
* Leave format in field or remove format
* Design to reduce user contention
* Allow max data rows in a page (8060 bytes available on 8k page)
* Store text, images inside or outside table
* Don't automatically add identify columns to your table
* Design for replication
* Check constraint can speed up query processing
* Perform regression test and trend analysis when promoting code
* Constraint wins over trigger
* Don't implement redundant integrity features


Ref: http://www.sql-server-performance.com/tips/database_design_p1.aspx