Relational Database Design: A Best Practices Primer

Relational databases are the dynamic compartments of fundamental web and enterprise cloud applications. Relational databases normalize, sort and link data using tables and queries. The design is open to the interpretation of the database designer, but a poor database design can be disastrous for any business.
A poor design can slow performance, inhibit scalability and create bugs in an enterprise application. To avoid the pitfalls of poor design, a good design plan is critical.
Primary and Foreign Table Keys
Primary and foreign keys link tables together, so developers can query multiple tables in one Structured Query Language (SQL) statement. All tables require a primary key, and any tables that reference another table requires a foreign key to that primary key. For instance, if a database consists of customer information and the related customer orders, a primary key named “CustomerId” can be created in the “Customers” table and a foreign key named “CustomerId” is created in the “Orders” table. The customer ID field is always unique, so each customer is uniquely queried with its linked order. The result is that a programmer can query and display a customer with the customer orders.
Table Indexes
Essentially, the primary and foreign table keys create an index for the tables, but secondary indexes can also be created. Typically, indexes are created on fields that are used in the SQL “join” statements or columns where the programmer queries based on these column values. Indexes sort values for faster lookups and queries. Indexes can mean the different between a query that takes 10 minutes to run and 5 seconds to run.
Types of Table Relationships
Relational databases have two fundamental relationships: one-to-one and one-to-many. There is also the many-to-many relationship, but this type of relationship is not a normalized standard. Many-to-many relationships should be broken down to a one-to-one or one-to-many relationship.
A one-to-one relationship means one record in a primary table links to only one record in a secondary table. For instance, a customer can only have one login identity. The table design can include a main “Customer” table with the customer’s information, and then a “Login” table contains the customer’s login identity and password.
A one-to-many relationship means one record in the primary table links to several records in the secondary table. An example is the customer and orders relationship. A customer has one account, but a customer can have one or many orders. Linking the customer and orders table will result in several records dependent on the number of orders processed by the customer.
Table Normalization
Table normalization is standard in the industry, but each programmer and database designer has his own idea of proper normalization. Normalization is the process of reducing data duplication, increasing data stability and facilitating streamlined updates and deletions of data.
For instance, a new database designer might put “option 1” and “option 2” as database columns for a customer order. However, what happens if the customer has a third option or the business wants to expand to allow for a third option? This table design does not facilitate scalability for customer options. Using this example, the right way to normalize the database is to create an “Options” table and use primary and foreign keys to link the order and option tables. The “Options” table has a foreign key that contains the order number, and the SQL can link to an unlimited amount of options for the customer order.
After the basic database design is set up, there might be some tweaks and changes made to the layout after deployment. This is common with new database layouts, but these fundamentals will help the database admin fall into common configuration pitfalls that impede business growth based on the technology.
Jennifer Marsh is a software developer, programmer and technology writer and occasionally blogs for Rackspace Hosting.