Personal Video Database

English => Help => Topic started by: rick.ca on September 27, 2010, 09:07:33 am

Title: 1050 Database Design
Post by: rick.ca on September 27, 2010, 09:07:33 am

(http://www.videodb.info/help/images/NavRight.png)  On this page:
Introduction (#post_intro)
The PVD Database (#post_s1)
Your Existing Collection (#post_s2)
Data Sources (#post_s3)
Building the Initial Database (#post_s4)
Maintaining the Database (#post_s5)


Introduction


(http://www.videodb.info/help/images/NavUp.png) (#post_top)(http://www.videodb.info/help/images/NavDown.png) (#post_s1)
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...



The PVD Database


(http://www.videodb.info/help/images/NavUp.png) (#post_intro)(http://www.videodb.info/help/images/NavDown.png) (#post_s2)
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!)

(http://www.videodb.info/help/images/Database Structure.png)


Much of this need not be of any interest to the user. But understanding the basic structure of the database in terms of relations (http://en.wikipedia.org/wiki/Relation_(database)) 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


(http://www.videodb.info/help/images/NavUp.png) (#post_s1)(http://www.videodb.info/help/images/NavDown.png) (#post_s3)
Many, if not most users have sought out PVD because they have an existing collection of videos. A "collection" might be...


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


(http://www.videodb.info/help/images/NavUp.png) (#post_s2)(http://www.videodb.info/help/images/NavDown.png) (#post_s4)
For most users, the primary source of metadata will be the IMDb (http://www.imdb.com/). 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 (http://en.wikipedia.org/wiki/Billing_(filmmaking)) 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...



Building the Initial Database


(http://www.videodb.info/help/images/NavUp.png) (#post_s3)(http://www.videodb.info/help/images/NavDown.png) (#post_s5)
To keep this simple, let's assume...


You'll want to import into PVD...


You'll create your initial database by...


This process is described more fully in the next topic, Importing Existing Collections (http://).


Maintaining the Database


(http://www.videodb.info/help/images/NavUp.png) (#post_s4)(http://www.videodb.info/help/images/NavDown.png) (#post_s5)
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.

(http://www.videodb.info/help/images/Tip.png)  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.


(http://www.videodb.info/help/images/NavHome.png) (http://www.videodb.info/forum_en/index.php?board=11.0;sort=subject)(http://www.videodb.info/help/images/NavLeft.png) (http://www.videodb.info/forum_en/index.php?topic=2099)(http://www.videodb.info/help/images/NavUp.png) (#post_top)(http://www.videodb.info/help/images/NavRight.png) (http://www.videodb.info/forum_en/index.php?topic=2128)

[attachment deleted by admin]
Title: Re: 1050 Database Design
Post by: rick.ca on September 28, 2010, 03:29:59 am
Reserved
Title: Re: 1050 Database Design
Post by: Gwandad on February 01, 2011, 06:20:03 am
I have a database of 450 movies. When I start PVD the status bar never shows the full number, generally around 250. Is there a known problem, or am I doing something dumb.
Title: Re: 1050 Database Design
Post by: rick.ca on February 01, 2011, 07:21:52 am
You probably have a filter set. From the Filters menu, select Reset filters.
Title: Re: 1050 Database Design
Post by: dma on September 05, 2012, 09:38:20 am
I cannot find the "medialabel" (nor "medialocation") field in the "movies" table of my actual PVD database.

I thought maybe it was due to the database having been created in an earlier version of PVD.
So i tried initializing a new database just to check which fields would now be present in this table, but its structure actually hasn't evolved.
Then i don't know why is this field missing (and it doesn't seem that it would be a custom field, as some functions of the software rely on that field).
Title: Re: 1050 Database Design
Post by: rick.ca on September 05, 2012, 12:12:40 pm
[MediaLabel] is a linked to the labels table via MovieLabels. [Location] is in the movies table.