From the course: Developing Microsoft SQL Server 2016 Databases

Table design in SQL Server - SQL Server Tutorial

From the course: Developing Microsoft SQL Server 2016 Databases

Start my 1-month free trial Buy for my team

Table design in SQL Server

“

- [Instructor] Tables are at the heart of every database and there's no better place to start understanding how to leverage the capabilities of SQL Server than to take a quick dive in to their structure. Tables organize data in to rows and columns similar to a spreadsheet. Every row in the table represents a unique record that describes one entity and you can store as many rows as your server hardware will hold. Each column stores information about various attributes about the entity. For instance, in a table about products each row would represent a single product and the columns describe the product name, unit price, quantity on hand and manufacturer. Tables can have up to 1,024 columns but the vast majority of tables that you'll work with will probably include far fewer, maybe a dozen or less. Both tables and columns can be defined with various properties that work to control the storage of data so that your server can protect against erroneous data entry or efficiently predict how the data will be used. For instance, you can specify that orders must be placed on a weekday or that product inventory on hand can't exceed a certain threshold. These constraints help insure that the data being stored is trustworthy and complete and follows the rules outlined by your various business use cases. In order to get your database design off on the right foot, it's important to keep the following best practices in mind when it comes to creating tables in SQL Server. First, let's talk about Naming Conventions. You should avoid table names and column names that contain spaces and symbols. If you do use these characters, you'll have to wrap all of your commands to the server that include these names, inside of square brackets. This is more difficult to read and longer to type so it's best just to avoid the requirement. To get around the use of spaces, consider using camelCase or PascalCase when naming objects. Both of these conventions join multiple words together into a single string and then capitalize the first letter of each word. The only difference is that camelCase starts with a lowercase letter and PascalCase starts with uppercase. Another naming technique uses the underscore character to join words together. Something called snake_case. Whichever option you choose to go with, make sure that you apply that convention consistently throughout the database. It'll make your life a whole lot easier when you don't have to remember how a particular object was named. When it comes to naming tables in particular choose either singular or plural names and stick with that throughout the database. Some people prefer having table names such as person, product and employee and for others it's more natural to consider a people, products and employees table. Neither way is right or wrong, just apply your preference consistently. Finally, you should review a list of words that should not be used as object names in SQL Server. These reserved words include the names of internal system routines, functions and other keywords that have specific meaning to SQL Server. And it can get confusing if you suddenly have an object with the same name. You can find a full listing of the reserved keywords at this website, and, if you click on the link on the right hand side called Future Keywords, you'll see a listing of words that aren't yet reserved but may become reserved in future updates to SQL Server. It's best to simply avoid any of these. After naming considerations, you'll need to spend some time thinking about the types of data that you'll need to store. Will you be storing text or numbers? What is the range of values that'll be appropriate for this column and which values should be excluded? Will blanks or unknown NULL values be acceptable defined in a column, or will this completely break the logic of your business use case? For instance, and employee with an unknown middle name probably isn't much of a big deal but a product without a price can grind your business to a halt. At the same time, you don't want users to just make up data to store. So, maybe supplying a default value will serve as an appropriate fallback. Other considerations in table design include how tables relate to one another. Will you store everything there is to know about your product in a single table? Or does it make since to break that information out into separate tables that look at product details, inventory and manufacturer individually. If you choose to break out the information then there will need to be some consideration of how they link back together using Primary and Foreign Key constraints so that you'll know what manufacturer goes with what product, for instance. This process of breaking large data tables into smaller related tables is called Normalization. In short, Normalization defines a series of rules that tables should follow in order to be efficient storage mechanisms within a relational database such as SQL Server. I discuss Normalization in depth in one of my other courses called Relational Database Fundamentals. But, for our purposes here, we can get away with simply knowing that getting our database to follow the rules of third normal form, is generally considered good enough for most applications. Third normal form states that your tables should include a Primary Key column and that all of the columns in the table are an attribute of that Key field and nothing else. This was just a quick outline of some of the considerations that go in to creating tables in SQL Server. With a little up front planning, you can ensure that your tables will be properly defined and suited to your individual business requirements.

Contents