I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex has been covered extensively and maybe case studies in the book.
6 Answers
Both of these guys authored several books, but I'm going to link to their blogs in case you want something more immediate.
Louis Davidson: http://drsql.org
Paul Nielsen: http://www.sqlserverbible.com/
SQL Antipatterns is worth a read for some hints on what you shouldn't do and why not (and under what circumstances those rules can be legitimately ignored).
Database in Depth: Relational Theory for Practitioners, by C.J. Date provides a great, short and highly informative introduction to database design.
It is important to distinguish between conceptual, logical, and physical levels of design.
Conceptual Level
Two excellent and complementary resources are:
- David Hay's Enterprise Model Patterns. This book lays out the basic patterns found in most enterprises and provides wonderful instruction on clear thinking about how to model the world. It focuses primarily on discovering entity types and relationships.
- Fabian Pascal's paper Business Modeling For Database Design. This paper is the first in Fabian's Practical Database Foundation Series and is the perfect companion to David's book as it focuses primarily on the discovery and classification of all the various business rules and how they will map to constraints in a logical database design.
Logical Level
The relational model is distinguished from all other data models in that it is a logical model which describes a logical structure with which to present data (the relation), a set of operators to manipulate that logical structure (the relational algebra), and a set of data integrity rules to ensure the data stored in a DBMS is as faithful a representation of the real world as possible. Three excellent resources to learn about logical database design are:
- Fabian Pascal's Practical Database Foundation Series (as referenced above). The remaining paper's in this series provide a concise and easy to understand introduction to the various parts of logical database design. Fabian's gift is his ability to distill very complex topics correctly into language the rest of us can understand.
- Toon Koppelaars and Lex deHaan's Applied Mathematics for Database Professionals. This wonderful book lays out a sound methodology for logical database design rooted in set theory and logic. It presents the fundamentals and then also presents how to apply them to create a fully functional database in Oracle.
- CJ Date's Relational Theory for Computing Professionals. CJ Date's body of work is enormous and one can benefit from any and all of it. This particular book is a recent (2013) revision superseding earlier works that really lays out the basics of the relational database model.
Physical Level
The physical level is the sole place where performance, disk and memory storage structures, and scalability live. I do not specialize in this area but can say that mastering this level is primarily an endeavor to master the given DBMS you are working with. The DBMS is such a sophisticated piece of software that you are fooling yourself if you think you can master the whole thing, much less master more than one. For this reason I would recommend sticking with the conceptual and logical levels and creating a sound logical database design, and then working with a really good DBA who specializes in the target DBMS to develop the physical design. One really good source however for physical design that lays out the fundamental topics and options common to most DBMS' is Sam Lightstone, Toby Teorey, and Tom Nadeau's Physical Database Design.
Another good one:
SQL and Relational Theory: How to Write Accurate SQL Code - C. J. Date (2009)
Preview available on Google Books.
Before you dive into the literature, you may find this paper useful:
All for One, One for All by C. J. Date
[This paper's] aim is to pin down the exact nature of oneto-one, many-to-one, one-to-many, and many-to-many relationships. In particular, it tries to come up with precise definitions for these concepts [because] a survey of the literature certainly betrays confusion and lack of systematic thinking in this area