Welcome to the eleventh article in my series SQL Server Administration in 15 Minutes a Week. Over the last few weeks we have been looking at backup and recovery processes. Although information on backups could have waited until the very end of this series, I feel backups are an important topic that get over looked too much. Therefore I decided to cover backup information at the beginning of this series rather than at the end. Having covered backups for the most part, we are now going to switch gears and look at just how we create database objects in SQL Server. The topics for this week include:
– Relational Database Design Concepts
– SQL Server Data Types
– Creating Tables
Relational Database Design Concepts
Up to this point we have focused on the physical design of a database which includes things like files and filegroups. The physical design is focused on how storing data and the most efficient way to access data. Now we are going to start to look at implementing the logical design of a database which includes objects like tables and the relationships between them. The logical design is only concerned with modeling a real-world data scenario — it is unaware of and non-dependent on any one particular Database Management System. For example, I could use the same logical design to create a database in both SQL Server and Access, and I would come up with the exact same database as far as the logical design is concerned. However, the physical design for the two databases could be very different — the Access database would be made up of only one file, whereas the SQL Server database could be made up of many different files spread across several hard drives.
Another example is a database on SQL Server that gets another data file added to it. While the physical design has changed by adding another file, the logical design is still the same. The important point to get here is that the physical implementation of a database and the logical implementation of a database are two distinct concepts that are, for the most part, independent of one another.
I think I’ve said this already, but if not, SQL Server is a Relational Database Management System (RDBMS). So it makes sense that the databases that we are going to be working with are relational databases. Although understanding how to design a relational database is an important topic, this series is aimed at SQL Server Administration, the 70-228 exam, and physical design issues — not the logical design of a relational database (that is covered in the 70-229 exam).
If this is your first time working with a relational database of any kind or if you never got a good grasp on the design concepts, I encourage you to stop here and get a book on designing relational databases. While you could probably complete the rest of this series — and the 70-228 exam for that matter — without knowing how to create the logical design for a relational database, you will have an easier time understanding the rest of this series and be a much better admin if you understand the design concepts. Check out the following links to get started learning about logical database design:
ACC2000: Database Normalization Basics (Q209534)
If you have worked with other relational databases (such as Microsoft Access) before and words like normalization and foreign key are not new to you, read on.
