A good database table design is the difference between a few seconds in response time and several minutes. While this time may not seem like a huge difference, imagine waiting several minutes on a Web page for results to render. A slow database can mean the loss of readers and potential customers. For this reason, database design is a priority for any dynamic software development.
Always Add a Primary Key on the Table
Every table requires a primary key. A primary key must be a unique field in each record, and it cannot contain a NULL value. Some table designs incorporate auto-incrementing integers, but database administrators prefer other operators such as a date or social security number. The primary key is also used to identify a record such as a “Customer ID” field in a customer table.
Normalize the Tables
Normalization takes large, poor-performing tables and segments them into a logical table design. If the database implements too many “distinct” or “union” queries, then it is a sign the table needs normalization. For instance, a table with customer and order information is poorly designed. Customer information, orders, shipping address, and billing address are logical tables used to normalize a customer table.
Place Indexes on Common Fields
Indexes improve data reads, so the SQL Server returns a data set more quickly. Indexes are placed on fields commonly used in SQL queries. For instance, if the customer table is often used in queries and the social security field is used to find customers in the database, place an index on the social security field. Indexes are also beneficial on fields used in “join” statements when combining two or more tables in a “select” command.
Avoid Duplicating Information for Data Integrity
Storing the same data in more than one table leads to poor data management. If two tables contain the same data, and the SQL developer forgets to change both records, the data is now inconsistent. It also creates bugs and errors in the software. If information is needed for a logical connection between two tables, use the “join” statement to connect the tables together in the SQL queries.
Document the Table Design and Changes
It’s hard to keep track of each table field, especially when the database grows to dozens of tables. A data dictionary documents each table and field. Documentation presents a logical order of each table, where it joins and the data it contains. It also helps new developers learn the system when more stored procedures are created.
After the database table design, administrators can work on the programming to optimize SQL queries. Table design, stored procedures and SQL Server maintenance all play a role in optimal performance for a database. Keep these five tips when creating any table whether it’s for a large or small business application.