In my previous post, I mentioned why it’s a good idea to freshen up on database concepts and recommended the first chapter of a Springer ebook, Pro SQL Server 2008 Relational Database Design and Implementation. Louis Davidson, Kevin Kline, Scott Klein, Kurt Windisch. 2008. Springer. ISBN: 978-1-4302-0866-2 (Print) 978-1-4302-0867-9 (Online). So far it’s a very readable book with great real-world examples and a sprinkling of humor.
Chapter 1: Introduction to Database Concepts.
It’s recommended that one design a database with a proper understanding of the underlying concepts, despite urgent calls to come up with a usable product right away.
Database Design Phases
Consider future data needs. Plan carefully at the outset to avoid difficult changes and maintenance down the road.
Steps of database design process as defined in this book:
The conceptual phase is the high-level discovery and analysis of the entities and business rules involved with the data under consideration. This is where you determine your high-level data requirements. Note that no tables or other practical database structure is designed in this phase.
- Entities are the “People, places, and things” (p.4) required by the design. They are usually nouns. In the library context entities might be users, library locations and items in the catalog.
- Business rules can be thought of as processes and rules of operation, such as the definition of specific user groups and their renewal periods, or the format of the ISBN. Business rules can be very detailed or very broad, or anywhere in between. Conditional rules are possible; these may have to be implemented outside of the database itself.
The logical phase is where the designs from the conceptual phase become a blueprint. There is still no detailed tables or any practical database design in this phase.The following are defined in this phase:
- Entities. “People, places and things”. Examples: patron, book, library branch.
- Attributes for each entity. Attributes are qualities of the entity. Examples: patron name, patron type, patron ID.
- Normalization (explained later in book)
- Candidate keys. Candidate keys are potentially unique attributes of instances of entities. Keys are how the database accesses specific instances. Examples: patron ID, ISBN number.
- Relationships and cardinalities (explained later in book)
- Domains. The datatype for each attribute, and whether the data must be present or NULL is acceptable.
This is where the rubber hits the road — the information from the previous phase is applied to a platform-specific database with data types, tables, constraints, and triggers. One should not stray far from the original design, data and integrity constraints that were defined in previous phases.Database security is an essential part of implementation. DW notes that most relational databases are used with SQL, which is vulnerable to SQL-injection attacks. Do not allow for damaging or embarrassing hacks by leaving the back door open.
The so-called physical phase is where consideration is given to physical storage methods and for performance-tuning. In the case of VMs (Virtual Machines) and the Cloud, one may not worry too much about physical storage space, but performance may still be an issue.