Database Design

On this page:
Introduction
The PVD Database
Your Existing Collection
Data Sources
Building the Initial Database
Maintaining the Database

Introduction

PVD is, of course, a database application designed for videos. In this context, "database design" refers to the essential structure of the relational database that PVD creates, and the program logic that goes with it. Given this structure, there are many choices for you to make in what information is to be collected and how it's to be maintained. This may also be referred to as "database design"—that is, your design of your database. This topic will clarify the restrictions imposed by the former, and the range of possibilities involved in the latter. This will help you to...

Understand and respect why things work the way they do.
Appreciate and use the flexibility available.
Provide for data that is meaningful and consistent.

The PVD Database

The following illustrates just some of the relationships in the PVD database. (This is from an SQL query builder, but it's not a valid query!)

People are related to Movies by Career (producer, director, actor, etc.).
People, Movies and (award) Events are related by Awards.
People and Movies are separately related to Genre.
Users (borrowers) are related to Movies by Loans.
Movies and People each have their own relationships to Images
Episodes are actually Movies that are related to another movie (series) by Episodes.

Much of this need not be of any interest to the user. But understanding the basic structure of the database in terms of relations helps explain why many things work and appear the way they do. So, for example, understanding how People are related to Movies won't change the fact you may do so by recording a particular person in a career "field" of a movie. The program will record the appropriate relation, and you don't need to know there is no such career field in the movie record. But understanding it is really a relation that the database is maintaining may help you appreciate the importance of getting that information from one reliable source. More on that later...

Your Existing Collection

Many, if not most users have sought out PVD because they have an existing collection of videos. A "collection" might be...

An existing database maintained in other software;
A physical collection of media, without any existing metadata;
A simple list of movies (e.g., movies you've seen and/or want to see).

If you have any of these things, some or all of the information is likely important to you. With some planning and the right tools, you'll be able to capture and import this information into PVD. If you have an existing database, carefully consider this topic in its entirety. It's likely the only information that matters is the proper identification of the movies in the collection (i.e., Title and Year or URL) and any "personal" information about each movie (i.e., date seen, rating, comments, media label, etc.). Other information can (and will) be replaced by on-line sources that may be different than those used before. Also, your routines for maintaining the collection will likely change—and with it, the nature of the information you choose to collect.

Data Sources

For most users, the primary source of metadata will be the IMDb. Regardless of what you may think of the website, it is the largest, most comprehensive source of data available. It not only includes more movies than any other source, it includes complete and consistent credits data for most of its entries. Most of PVD's standard fields may be filled using the IMDb plugin.

Even if some data elements from other sources are preferred, the IMDb provides an excellent source for basic factual data, including credits. A good strategy is to get all the information provided by the IMDb plugin, and then replace elements of that from preferred sources, when available. At the same time, additional information from alternate sources may be added to custom fields.

If—because of language or nature of collection—the IMDb is not a viable primary source, consider alternatives that might serve the same function. That is, a source that...

Is likely to include the movies in your collection. Even if other sources are used, what you want from a primary source is the ability to correctly identify your movies through a simple title search, and provide accurate factual data. Identification and accuracy of basic information, of course, go hand-in-hand. A source that does not record a title's year and genre correctly, for example, will not identify your movies correctly.
Includes complete and consistently recorded credits data. While it's possible to combine credits data from whatever sources have it, it's preferable to get all credits data from one source. Otherwise, duplications and inconsistencies will likely occur.

Building the Initial Database

To keep this simple, let's assume...

You have an existing database that includes Media label, Date seen and the purchase price of each DVD owned.
You'll be happy to replace all other data with that downloaded from IMDb.

You'll want to import into PVD...

A complete list of all the Titles in your collection. If available, this should include data that will help find each movie's entry at IMDb. Ideally, that would be the IMDb URL—which you may have if it was previously used as a source. If not, Year will resolve most ambiguities.
Your "personal information" about each movie. Standard fields exist for Media label and Date seen. You'll need to add a custom field for Purchase price.

You'll create your initial database by...

Exporting the necessary information from your existing software.
If necessary, preparing this exported data so it's in a form that can be imported by PVD.
Importing the data.
Downloading data for each movie using the IMDb plugin.

This process is described more fully in the next topic, Importing Existing Collections.

Maintaining the Database

Once you've created your initial database, maintaining it will probably be straightforward. It's worthwhile to consider, however, exactly what this will entail and how it will be done. There's unlikely anything about the "design" of your database you can't change at a later date, but having a clear plan from the start will at least save you some time in adding the data. For example, if you later decide you should include the vendor and date (as well as the price) of each DVD purchase, you'll be able to add custom fields and import those separately. It would have been easier, however, to include these in your original import.

The process of adding movies to your collection might be different from what you've done in the past. The way PVD scans media might suggest a different way to reference physical media. Perhaps the reason you're using PVD is that you've decided to rip all your media to HDD—and you no longer even need any reference to the physical media.

Read or scan the rest of this documentation before finalizing your database design. You're likely to find ideas and suggestions which you will find easier to incorporate from the start.

[attachment deleted by admin]