Nancy Street - Music - Library Database Design

	Library Database Design

	Links Removed	Home « Music « Music Database Design

Introduction

In the mid 1990s I spent many weeks of hobby time entering all of the titles of the Nancy Street music, book and video library into a huge flat Access database table. I called this my "Nancy Street Entertainment Database". I planned to normalise the data and put a nice "front end" over it at some later time. Designing a well normalised database is a mandatory skill for all software developers. For more information see:

Database normalization (Wikipedia)
Description of the database normalization basics (Microsoft)
An Introduction to Database Normalization (mysql.com)

As the months passed, my casual attempts to create an elegant and concise table structure for my entertainment database eluded me. I wanted to be able to perform queries like:

List all vinyl comedy albums
List all classical recordings with Herbert von Karajan conducting
List all songs on CDs with Paul McCartney participating in any role
List all books about astronomy

To make things worse, I had to deal with problems like these:

An artist can appear on multiple albums.
Multiple people can be the author of one book or album.
One album can have multiple physical discs (Beethoven's piano sonatas are spread over 12 LP records for example).
An artist can appear on the same album in multiple roles (conductor and composer for example).
Multiple copies of an album might be in the collection, possibly owned by different people.
One album can be in multiple genres (a comedy soundtrack for example).
Multiple media types can be in a collection (audio CD, data CD, video DVD, LP record, vinyl single, etc).
Some media types have "sides" and some don't.
Vinyl EPs and 78s do not have a title like a regular long play album.
Some albums have totally unidentified performers and composers.

The way to overcome these problems is to perform agressive normalisation and split the database down into more smaller tables. There is an inverse price to pay for doing this. As you increase the number of smaller tables, the structure looks more frightening and coding against the tables becomes more complicated. To display details of a single artist, album or genre requires navigating through many links to related tables to collect the data from multiple tables. Doing this manually in code is dreadfully tedious and fragile. This is where ORM (Object-relational mapping) tools come to the rescue.

After the release of .NET and a new set of rapid development tools and UI designers I became aware of CodeSmith and the netTiers templates which could generate a complete data access layer from a database schema. There are many products and tools that perform the same (or similar) task as CodeSmith and netTiers, but I just happened to use this combination due to exposure during a job contract and I got to like them. See also: NHibernate, Entity Framework and MyGeneration amongst many others.

In Mid 2011 I started using Microsoft's Entity Framework 4 as a replacement for CodeSmith and netTiers. EF4 is full of quirks and the designer is buggy and under-powered, but I am finding it acceptable for writing serious apps. It took me months to figure out what best practises were in different scenarios. Simple tracked entities (the default type) are only useful for the most childish of tasks. POCOs are good for returning pick lists and table binding sets over services. Self-Tracking Entities (STEs) also seem to work well over services. In fact STEs are close to what netTiers generates, but the netTiers entities and collections have far richer features. As a hobby project I'm writing a WPF app as the front-end over my library database and I'm using EF4 as the data layer (see Jade).

Think 3-D

I must stress how useful ORM tools are. Thanks to them I can now confidently sit down to design a database that is agressively normalised and not have to worry about how to code against it. All of the code necessary to access the database and navigate the complex structure is auto-generated by a tool. When designing database tables my vital advice is "think 3-D". What I mean is, don't create wide flat tables, but split everything down into more narrow joined tables. Here is an example of a naive bad table design for a table to hold books:

A naive "bad" table design.

With this simple flattened design you can only have one Author and one Publisher for each book. As time passes you may have to add more columns to the table to add more information to each book. This will create a maintenance nightmare.

Avoid this flattened approach and "think 3-D". The following relationship diagram shows a more mature and flexible table structure.

The concept of an "author" and "publisher" have been combined into a single "name" table, since both are simply names or people of companies. We now have the classic one-many-many-one relationship between books and names. It is now possible to assign many names to a book. We can take this a step further and add a "role" to the join which means each book-name relationship has a role associated with it. We can now have names whose role is publisher and names whose role is author, or any other roles you can think of.

The normalisation could be taken further. The Name table has an Email column, which restricts one name to one email address. If we perform the same process again we could create an Email table and a one-many-many-one join between the Name and Email tables, allowing a name to have many email addresses.

With the database and software tools available these days there is no excuse to not normalise your data down as far as reasonably possible. Doing so gives you the flexibility to run complex queries in different directions along the joins to find whatever information you need. An ORM tool can generate the code necessary to traverse the relationships easily. NetTiers for example has the concept of a "deep load" and "deep save" where a single record can be loaded and then all of the related child records can be loaded into nested objects. You can walk down as many levels of relationships as necessary during the deep processing. Entity Framework allows relationship navigation and loading as well, either via lazy loading or demand loading.

The Nancy Street collection database

My first enertainment database with about 18 tables was created Access, then in SQL Server Express, but a few years later I decided that SQL Sever was too heavyweight for my needs. I planned to give copies of my application and database to non-technical friends and it was far too complicated to have a dependency on SQL Server.

In early 2009 I decided to migrate my entertainment database from SQL Sever Express to SQL Server Compact Edition 3.5 (SQL CE). The installation footprint of SQL CE is quite small and you only need to run a single 3MB installer to prepare the environment. Moving from Express to CE meant of course that I couldn't use stored procedures or views, so I spent more hobby time converting all queries over to plain SQL command statements.

In early 2011 I decided to migrate my database to SQLite and call it the "collection database". SQLite has no installation footprint at all, it's just a reference to a DLL that can be distributed with the application installer. The ADO.NET provider works perfectly with Entity Framework and I plan to use SQLite and EF4 together in many future apps that need a lightweight database.

Click the thumbnail image on the left to popup a 50% size zoom of the diagram of my current database. The diagram may look intimidating at first glance, but I've colour-coded groups of tables to help clarify the meaning of different groups of tables.

NOTE: By early 2012 the design of the database had expanded somewhat to make it more general and add new functionality. The overall structure remains the same, but tables have been added and some columns have changed. Click THIS LINK to popup a magnified screenshot of a complete EDMX designer diagram of the January 2012 database (image size is 738 x 915). I am using the new SQLite database is an application called Jade.

Tables

Volume	This is the central table which defines a uniquely identifiable item in the library, such as a book, a video disc, a cassette tape, etc. The important thing is that each item in this table is identified by some unique ID such as a book ISBN, a music catalogue number, etc. A single volume can contain multiple physical objects, a boxed set of CDs for example. Even though there are mutiple discs in the box, the box has a catalogue number that identifies it to the world, it is therefore a single row in the Volume table. You may have multiple physical copies of the same Volume. For example, two people in the same home might both own the same book. Multiple physical copies of a Volume are represented by the Instance table.
Instance Owner Location	An Instance represents a physical copy of a Volume. Each Volume must have at least one Instance associated with it. The green part of the diagram shows how an Instance is associated with an Owner and a Location. Say for example Bob and Alice both own a copy of the book The Hobbit, Bob's copy is in the bookshelf and Alice's is in the garage in a storage box. This table structure can tell us who owns each Instance of the book (a Volume) and where it is. The Jade application does not use the words Volume or Instance in the UI, the distinction between the two is hidden by program logic and users only see references to objects or titles in the collection.
File Track	These tables are simple one-to-many children of the Volume table. A Volume such as a data CD can contain files and a compact disc can contain tracks.
Media	This table is a simple many-to-one child of the Volume table. Each Volume must be of a specific media type.
VolumePicture VolumeTag VolumeGenre VolumeName VolumeLink	These tables (shaded yellow) are the middle of the one-many-many-one relationships with the Volume table. These tables allow a Volume to be related to many pictures, tags, genres, names and links.
Picture Tag Genre Name Link	These tables (shaded blue) are the other side of the one-many-many-one relationship with a Volume. A "tag" is an artitrary piece of information associated with a Volume such as "on loan", "damaged", etc.
Role	This table enhances the Volume-Name relationship by associating a role with each one. A single name can now be in multiple roles on Volume. One person might be the performer, arranger and producer of a single album for example.
PictureType	Each Picture needs to categorised as a certain type (album front cover, book sleeve, etc), so this table exists for that purpose. Making a "descriptor" or "grouping" table like this is a common technique in data normalisation.
NamePicture	Just as there is a one-many-many-one relationship between Volume and Picture, I found that pictures needed to be associated with names. Rather than have two picture tables, this join table allows a Name to be associated with multiple pictures.

Downloads

SQLite_database.zip (2.7MB) - A zip of the SQLite database containing the Nancy Street entertainment library. The unzipped file jade_migrate.db3 is about 8MB in size. The name Jade refers to a new WPF application I am writing over the database. The migrate refers to the fact that it has been converted from the original SQL Server Express database.