Author: Louis Davidson &Jessica Moss
Aimed at: programmers needing to design a relational database
Reviewed by: Kay Ewbank
If you need a book on this topic this one comes highly recommended.
When you pick up a book with a title such as Relational Database Design and Implementation, you probably don’t settle back with the thought that this will be fun. Not even if you’re a true database enthusiast. However, I can recommend this book wholeheartedly if you need to know about how to design a database from a programmer’s viewpoint.
The book starts with an introduction to the fundamentals of database. Chapter 1 sets out the basic terms and concepts, with an introduction to Codd’s rules, data structures, relationships and dependence. Chapter 2 gives some advice on how to work out what is actually required, with suggested questions to ask the client, and ideas on how to gather the information. Sadly, the questions you’d really like to ask the client about their sanity and lack of any idea about what a database actually is are missing, but it’s probably not a good idea to raise such issues anyway. Davidson shows his real life knowledge, though, with tops such as ‘never expect the data you’re converting to have any quality’. Now there speaks a man who’s imported data a few times!
Chapter 3 covers the language of data modeling, concentrating mainly on the use of IDEFIX as the language for data modeling, with brief intros to alternatives such as Chern, Visio, and Management Studio Database Diagrams. Having introduced data modeling, Davidson moves on to look at the production of the initial data model containing details of the tables, relationships and business rules. Normalization is covered in Chapter 5, with good descriptions of the principles, and a set of clues that might indicate that a design is not normalized.
Having worked through the concepts, Davidson then goes on to look at case studies showing how to create working databases, starting with using DDL (Data Definition Language). This chapter is the first time that SQL Server 2012 is used within the book; until now the descriptions have applied to general database theory but now Davidson moves to some SQL Server specifics. Chapter 7 looks at the use of Check Constraints and DML (Data Manipulation Language) Triggers to provide data protection. Chapter 8 looks at patterns and anti-patterns. Patterns are those elements of a database you meet over and over in different projects, and that are good to use. Anti-patterns are those elements you might well see (in other people’s designs, natch), that are definitely not good to copy. Database security and security patterns get their own chapter, showing how to control access to data using T-SQL. There’s an interesting chapter on table structuring and indexing that seems at first glance to be a bit late on in the book in terms of content organization. However, the actual material in the chapter is addressing the idea that by this stage, you’ve got the data quality issues under control, so now it’s time to look at how to return information in a reasonable time when a user runs a query.
Returning results in a timely fashion for one user is relatively simple compared to the problem of keeping multiple users happy, and coding for concurrency is the next target for Davidson. He looks at transactions, coding for integrity and concurrency, and considers topics such as pessimistic and optimistic locking. There’s a chapter on how to create and re-use standard database components, those elements that Davidson has found useful in the databases he has worked on. Data access strategies for how best to implement and distribute the business logic are covered in Chapter 13. Davison looks at stored procedures versus ad-hoc SQL, and discusses the pros and cons of each. He also covers the integration of T-SQL with CLR, the Common Language Runtime, which lets you use the.NET CLR engine within SQL Server. This is obviously a huge topic, but Davidson gives a useful overview of the pros and cons of when to stick with pure T-SQL and when the other languages might be better. The final chapter was written by the co-author Jessica Moss, and covers designing reports, including an introduction to dimensional modeling.
This is a very readable book. Davidson’s knowledge of SQL Server and database design is first class, and he writes as though he was chatting to you over a cup of coffee or a glass of beer. He’s not afraid to give you his own personal take on a topic, and his descriptions are clear and lucid. The subject matter might not be a topic you’d think would make for an interesting read, but Davidson manages it.