Table Design, Best Practices

Table design is one of those little idiosyncrasies that developers rarely follow, but the best table design can create the best scalability and performance value for a company application rather than worry about it when problems arise.

Normalization

It can’t be emphasized enough that normalizing tables is extremely important for performance and data integrity in a corporate database application. Normalization is the act of creating tables that keep performance at its finest and limit redundant data. Normalization will reduce the amount of data that needs to be stored and overall reduce the I/O at the database table level.

Table Indexing

Table indexing is one of the most important aspects of table design. There are different theories on the most efficient way to create indexes. Without indexes, queries on table data can take exponentially more time than with a precise index. A table index affects the sorting of the data in a table that so when querying on a sorted field it will make searches much more efficient. Indexes should be well designed, but you can over index a table actually hurting performance. They should be mainly on popular queried fields without over indexing to hurt performance. Overall, there should be only two to three indexes on a finely tuned table.

Use Appropriate Data Types in Columns

Data types are highly important when it comes to high end SQL queries. Using TINYINT over INT can be a performance issue when complex queries with many mathematical functions are being used. Also the use of decimal numbers should be evaluated before randomly placing the data types in tables. When designing tables, use the smallest data type possible. Also, it is best to avoid the blob or long text values. One huge issue with these data types is that they cannot be efficiently searched and they have extreme overhead on the SQL server when including them in a query.

Stored Procedures to Query

The idea of a stored procedure is that the execution plan is compiled and cached so that next call is quick, which of course carries over to better performance. Instead of recompiling a new execution plan, SQL server will simply rerun the cached version. It can create an extremely faster search for organizations that use the same searches continuously.

Whenever designing a table, make sure to create the best table layout possible for performance. Nothing is worse than the need to redesign a table after it has been promoted to a production environment, so be proactive.