philduba.com

SQL Standards
- Naming conventions (tables, databases, stored procedures, indexes, etc.)
- Installation procedures
- Server/DB/Storage configurations
- Make some and stick to them

3 Rules to DB Design Success
- Take time to understand indexing - same and if not more important than table schemas for performance/maintenance
- Realize there is more than one way to get the same results. Don't go into design with blinders/preconceived notions and don't be afraid to change after testing.
- Don't design alone - must make sure that the design meets the functional and architectural requirements. Most developers focus on functional but don't take in to consideration the architectural needs of the database. DBA's tend to think the opposite. Architecture - everything from storage size, data amounts, boxes needed, archiving, etc.

Database Design
- poor design leads to most reasons applications perform slow.
- Poor design examples - comma separated lists, flat tables (way too many columns), no identity/sequence columns but instead use a NextVal table used across all tables - Nate's example - an auditing table with 175 columns and 20 million rows and the table takes up 40GB of memory
- Good design - no redundant data, indexes used properly, well documented database

Design Phases
- Conceptual - sketch of database, high level entities - database platform agnostic
- Logical - refine conceptual, define attributes and relationships - database platform agnostic
- Implementation - adapt model to the RDBMS
- Physical - actual creation of the database, physical layout (server, storage, indexes, etc.)

Relational Data Structures
- Information Principle - all information in a relational database is represented explicitly at the logical level in exactly one way - by values in tables
    - Uses itself to work, meaning everything is self-contained within the db
- Standard items (Database, Schemas, Tables, etc.)

Keys
- Primary Keys - 1 or more but be careful with composite keys due to complexities in joins, etc., make sure indexed so pick a representable datatype for a key
- Foreign Keys - maintain dependent values between tables and forces relationships - deletes become more difficult

Normalization
- No redundant data
- Attributes represent one value - i.e., no arrays or delimited data in the database with some exceptions (phone numbers, email addresses) - not sure I agree with allowing that
- Attributes must not be a fact of another entity - avoiding maintaining values in multiple tables (countries, states, counties, etc.)
- Keep tables thin - recommends 10-15 columns per table
- Avoid unnecessary coding - queries become too difficult to implement
- Ability to have useful indexes
- Easy to over normalize/index and can cause bottlenecks - test thoroughly

Indexing
- understand the different types of indexes
- keep them simple
- too much can decrease performance - I've seen this first hand
- view the execution plan to see how indexes affect
- maintenance is important, reindexing is important (standard of reindexing once a week, and rebuild once a month), avoid index fragmentation
- Clustered Indexes - one per table, data is physically ordered. Don't put on table with massive inserts or data inputs
- non-clustered indexes - unique or non-unique, data not physically ordered. most time on primary key but doesn't have to be.
- use in fields matched in a where clause consistently, used on joins, order by and group by fields
- don't use on everything, fields search by '%expression%' (like)

Indexing Example
- Created small table with no primary keys or indexes only 3 columns
- inserted 100,000 rows, total of 200,000 rows
- did a query for one row, initially took 46ms
- added an index on column in where clause
    - Talked about fill factor - by setting fill factor can increase the database performance due to when a new page is created. good start is 70-80 and move according to table's usage. This was SQL Server in the example, I wonder if there is something similar in Oracle (which is what we use)
- Took 46ms again, but execution plan was different (from table scan to index)
- Second run went to 0 - point was that every ms saved counts and if this query runs thousands of times a day, just think about the performance saved

Performance
- Considers anything over 500ms to be a slow query if used quite a bit on the application
- Don't concentrate on queries run once a day or much more infrequently
- every ms counts

Design for Performance
- Design is a major factor in performance
- Normalization vs. De-normalization
- Partitioning
- Prepare for & handle high traffic
- Hardware - don't forget the hardware