The database is the anchor of any application that has to transact against data. If the database design has errors or is incomplete, the application will sooner or later begin to fail or become problematic, either because it will replicate database design bugs or because it will store or update data inconsistently.
Here are six tips to get your SQL Server database design started in the right direction.
Tip 1: Follow a Naming Convention for Database Objects
There are many advantages to having a naming convention, and several authors have published guides (see here and here). First, a naming convention gives your design a polished look. Second, it makes code easier to read. In the naming convention I follow, all tables have the “tb” prefix, as in tbCustomer, and they express concepts in singular form (i.e., not tbCustomers). Views, on the other hand, have the “vw” prefix. When I see a query, for example, I can immediately tell what is a table and what is a view. Moreover, a prefix prevents conflicts with SQL Server keywords (a conflict may not always be a problem, but SSMS always colors them different, and this is distracting sometimes). Next, a naming convention may help execution efficiency, as when using “sp” as the prefix for stored procedure names, rather than “usp_”, which signals SQL Server that this might be a system stored procedure, as explained here. Finally, if there is more than one designer, a naming convention helps keep the design coordinated and ensures a uniform-looking result.
Tip 2: Name Objects Properly and Uniformly
This might seem related to Tip 1, but it refers to using proper mnemonics for tables, views, variables, etc. You want your design to be as self-documenting as possible, and proper names are key. This has two parts. First, name the objects to reflect the domain you are working on and the way end-users refer to concepts. For example, if in a manufacturing plant products are assembled in what look to you like “rooms” but employees call them “modules”, then use “module” in your design, not “room”. Second, avoid using meaningless names such as “#MyTempTable” or “@Var1”. Name objects for what they mean, like “#tbActiveCustomer” or “@NewClientId”. When you come back to the code six months later, you will thank yourself for spending time coming up with proper names.
Tip 3: Ensure your Design is Normalized
Database normalization is the process of organizing tables and columns, and relationships between tables, in order to minimize redundant data. A properly normalized database design protects data consistency by reducing—and almost always removing—the possibility of insertion, deletion, and update anomalies. A discussion of the technical details of normalization is beyond the scope of this post, but see this article for a quick overview of first, second, and third normal forms.
Tip 4: Declare all Foreign Key Relationships and Build the Diagrams
By explicitly declaring FK relationships, SQL Server will enforce them and preserve the integrity of related data (hence the term “referential integrity”); this is especially important for keeping honest your application’s data management logic. And by drawing the diagrams in SSMS you leave the relationships documented.
I like to declare FKs while building the relationship diagrams, because of the drag-and-drop interface and because when I have data type conflicts I can correct them right there. The most common conflict I encounter is incompatible data types, and it is mostly due to typos or oversights, as when a column ClientId in a tbClient table is of type int but I mistakenly declared it with another type in the tbOrder table.
Tip 5: Use Proper Data Types
The first reason for using proper data types that comes to mind to many of us is the optimization of storage, and its concomitant performance advantage. But for clustering keys, it is also crucial to use small and ever-increasing values, such as integer identities, to prevent or reduce index fragmentation, as explained here.
This is related to the use of surrogates as primary keys. For example, while we might be tempted to use SocialSecurityNumber as the primary key in a tbClient table, secured in knowing that a person’s Social Security Number never changes, we have to accept that data entry errors will occur. When they happen, the application will not only have to update the PK but also all tables with FK relationships to tbClient, thus making it a complex transaction. If a surrogate key is used, such as an integer identity, SocialSecurityNumber is just another column to update in a single table.
Tip 6: Use Schemas for Organization and Security
Introduced in SQL Server 2005, a schema is an organizational and security mechanism. It acts like a container of database objects, creating a separate namespace that may be independently secured. The AdventureWorks sample database declares the schemas “Human Resources” and “Sales”, among others. This aids in documenting the design and allows separate security policies to be defined on each schema.
