[DecK]

As my post here got no traction at all, I've decided to take it a step further and work up a prototype for a new video database. The attached files consitiute that prototype.

This prototype has the ability to ;

• support multiple profiles.
  
• add new media types (Home Movies, Documentries, etc.) to the library without the need to add new tables.
  
• add new types of artwork (CDart, ClearArt, etc.)without the need to add new tables or even columns to existing tables.
  
• associate any artwork type with any media type in the library.
  
• allow movies to be in multiple sets.
  
• allow sets to contain any media type, including other sets.
  
• allow sources to be updated (location change) without having to rescan all items.
  
• be fully backwards compatible with the current structure (this one is aspirational at the moment)
  

As part of this work, I was able to rationalize some of the data from the existing structure and in my sample database the db size was reduced from 10,056KB to 4,572KB.


The design includes 25 tables, categorized into three broad groups as follows.

Lookup Tables
All these tables are of the form (ID,Name)

1/ NodeType - contains a list of media types, each entry in the library will be of one and only one of these types.
Values in the sample database are
0 Unknown
1 Movie
2 Episode
3 Music Video
4 Season
5 TV Show
6 Set
7 Genre
8 Studio
9 Country
10 Tag

2/ BookmarkType - contains a list of bookmark types.
0 Standard
1 Resume
2 Episode

3/ StreamType - contains a list of stream types.
0 Video
1 Audio
2 Subtitles

4/ ArtType - contains a list of artwork types.
1 Poster
2 Thumb
3 Banner
4 Fanart
5 CDArt
6 ClearArt
7 Logo

5/ LinkType - contains a list of the types of links that can be created, either between entries in the library and other entries or between entries and people.
1 Node has Actor
2 Node has Director
3 Node has Producer
4 Node has Writer
5 Node has Genre
6 Node has Studio
7 Node has Country
8 Node has Tag
9 Node in Set
10 Tv Series has Season
11 Season has Episode
12 Movie Links TV Show

6/ Scraper
Contains a list of all scarpers that are available to media sources.

7/ Profile
Contains a list of all profiles.

File Tables
This set of tables is used to manage the information about the files and structures used to populate the library.

8/ Source
Contains all File Sources. (Basically the records from the current Path table, both only those that are root sources).Has FK's to NodeType and Scraper.

9/ ProfileSourceAccess
This table is used to control access to content within a source based on the current active profile. has FK's to Source and Profile.

10/ Directory
Contains the set of directories under each source. Only relative paths are stored. Has FK to Source.

11/ File
Contans 1 row for each file within a directory. (Different from today's files table, in that it doesn't merge stacked files and doesn't contain history data.) Has FK to Directory.

12/ FileSettings
Contains the same info as the current settings table, but has a composite key of File and Profile to allow for per profile settings to be saved.

13/ StreamDetail
Unchanged from the current structure except for a FK to StreamType.

14/ NodeLinkFile
Used to join files to their coresponding item in the library tables. It also is used to store the data required for File stacking and File spliting.

Library Tables
The library tables contain all the data need to navigate between items (nodes) in the library, as well as manage the metadata for each node.

15/ Person
A rationalized version of the current actor table, with the ability to store local thumbs in a more space efficient manner.

16/ Artwork
A rationalized version of the current art table, with the ability to store local art in a more space efficient manner.

17/ ProfileHistory
Used to store playcounts and last played data on a per profile basis.

18/ Bookmark
Contains the same info as the current table, but has a composite key of File and Profile to allow for per profile bookmarks to be saved.

19/ Node
This is the central table of the library sturcture. It contains a record for every node within the library. This table replaces all of the following tables (country, episode, genre, movie, musicvideo, seasons, set, studio, tag, tvshow).
Only relatively common data is stored in this table.
For attributes of a media type that are unique to that type (album on MusicVideo for example) an auxiliary node table is included. The PK on each aux table is the same as the PK for the related record in the Node table.
The set of auxiliary node tables is as follows.
20/ NodeAuxEpisode
21/ NodeAuxMovie
22/ NodeAuxMusicVideo
23/ NodeAuxTvShow

24/ NodeLinkNode
Contains all relationships between items in the library. Replaces all the link tables (except links to people).

25/ NodeLinkPerson
Contains all relationships between items in the library and people. Replaces all the link to people tables.

A Concrete Example
Rather than just making this an academic discussion on the pros and cons of various DB designs, I have included a set of scripts to allow developers to migrate a copy of an existing DB to this new structure.
All files can be found here.

• BuildNewStructure.sql contains the scripts needed to add the new tables to a SQLite database.
  
• MigrateData.sql will move data from the existing tables to the new structure and then delete the old.
  
• MyVideos75.db is the db I am testing with.
  
• MyVideosDK.db is the output database.
  

Notes on the scripts.
All new tables are prefixed DK_ in the sample.
FK constraints are there only as aid to understanding.
I have not added any indexes yet, so performance is not as good as it could be.
There are lots more triggers to be written.
Migration script will not migrate the following - (musicvideos, stacked files, last played, play count).
Migrtation script is merely intended to give a developer a good enough working version of the new structrue. DO NOT USE IT ON YOUR LIVE DATA.
The build script contains two sample views to aid in understanding the structure. Much more work to be done here.
Not all legacy fields have been added to the NodeAux... tables. But I believe enough have in order to test the proposal.
Comments on my taste in movies and or tv shows will be ignored.

At this point I am looking for feedback of any kind.

Thanks
Declan

[pko66]

It seems a great idea to me, I find that the database is one of XBMC main features but, at the same time, it has a lot of shortcomings... this implementation seems to be better! a few comments/ideas:

- One of the worst problems I find in XBMC today is that the interesting feature of multiple profiles is not very practical. The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.

- It would be great to keep watched status and even better metadata related no to file but to ID (episodeID or movieID). That would allow among other things:
> keep metadata/watched status when moving files around
> keep metadata/watched status when you have more than one version or update the version you have of a work
(there are many reasons for multiple versions, for example, you change the file you have to a better quality one, or you keep a movie in 2 files, one dubbed in spanish and another in the original french language, and maybe sometime later you delete both and have another one with 2 audio streams, or you have a movie in 2 files, -CD1 & -CD2 and later you substitute them with a version in just one file, or you have a HD version to watch from powerful clients and a SD version to watch from android/xbox)

So, for each profile, when a new movie/episode is added, its ID determines the watched status. Also, when a movie/episode is being marked as "watched", all other files with the same ID in the database would be marked as watched also for that profile.
Some data will be related to the file (resolution, codec), some other to the ID (actors, year of production)

[jjd-uk]

The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.

Are you completely sure?

Not used Profiles for a while but you used to have the choice I'm sure of using a common database across all Profiles where you want to things like Watched status in sync, or you could use independent databases where is Profile is for a different users so stuff like Watched status needs to remain separate.

Taken from wiki

Media Info
Allows you to have separate information saved for your media, i.e. IMdB, AllMusic, etc. The options are
Separate - Profiles have separate media info with full control
Shares with Default - Shares media info with the default user with full control
Shares with Default (Read Only) - Shares media info with the default user, and is locked by the master code
Separate (Locked) - Profile has separate media info, but you can only change it by enabling master mode.

Doesn't Shares with Default allow a common database with all Profiles allowed to update the database.

[bnevets27]

The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.

Are you completely sure?

Not used Profiles for a while but you used to have the choice I'm sure of using a common database across all Profiles where you want to things like Watched status in sync, or you could use independent databases where is Profile is for a different users so stuff like Watched status needs to remain separate.

Taken from wiki

Media Info
Allows you to have separate information saved for your media, i.e. IMdB, AllMusic, etc. The options are
Separate - Profiles have separate media info with full control
Shares with Default - Shares media info with the default user with full control
Shares with Default (Read Only) - Shares media info with the default user, and is locked by the master code
Separate (Locked) - Profile has separate media info, but you can only change it by enabling master mode.

Doesn't Shares with Default allow a common database with all Profiles allowed to update the database.

I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.

[DecK]

The profiles are independent so, for example, you must update database once for each profile, that is complicated, time-consuming and difficult to automate in a centralized database setup. I hope your approach could correct or at least alleviate that.

Yes, this approach would mean a single database for all profiles which in turn means a single library update for all profiles.

It would be great to keep watched status and even better metadata related no to file but to ID (episodeID or movieID).

Watched status is related to the library node, not the file in this model. Same thing goes for metadata.

I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.

That is correct, and is one of the main issues that I am trying to address here.

[narvatu]

This proposal is only about VIDEO Database?
What about music, photo and overcoming games?

[jjd-uk]

I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.

So you want separate Profiles using a common database but still being able to set watched flags on a per Profile basis

[DecK]

So you want separate Profiles using a common database but still being able to set watched flags on a per Profile basis?

That is one of the things I want, yes.

I use two profiles and currently that means seperate updates (effort dupplication) and seperate databases (data duplication). Both of these are bad things from a design perspective. This proposal would remove these duplications.

[Martijn]

And what if I don't want the same in my two profiles? Profile 1 only source A and profile 2 only source B
Profile 3 part of source A and B

Profile 3 wants to have different scraper settings than profile 2 or 1

[DecK]

And what if I don't want the same in my two profiles? Profile 1 only source A and profile 2 only source B
Profile 3 part of source A and B

Profile 3 wants to have different scraper settings than profile 2 or 1

Use Case 1 above is already supported in this model. That is the purpose of the 'ProfileSourceAccess' table. In your example that table would look like this

Code:

ProfileID    SourceID
1                A
2                B
3                A
3                B

Use Case 2 is not one that I had considered quite frankly. It woud require a change in the design, but I believe that it may be possible. I do wonder, however, if this is a common requirement.

[Martijn]

It is possible now so it should still be.

[bnevets27]

I know that having separate profiles with separate watched flags will create different mysql databases. Which kills the advantage of having a shared mysql library. Essentially you can not have separate profiles and a single/shared mysql database at the same time.

So you want separate Profiles using a common database but still being able to set watched flags on a per Profile basis

Yup
Actually all I want is two profiles for watched status. Really two different coloured check marks on the same profile would be a fine solution for me.

[DecK]

And what if I don't want the same in my two profiles? Profile 1 only source A and profile 2 only source B
Profile 3 part of source A and B

Profile 3 wants to have different scraper settings than profile 2 or 1

Use Case 1 above is already supported in this model. That is the purpose of the 'ProfileSourceAccess' table. In your example that table would look like this

Code:

ProfileID    SourceID
1                A
2                B
3                A
3                B

Use Case 2 is not one that I had considered quite frankly. It woud require a change in the design, but I believe that it may be possible. I do wonder, however, if this is a common requirement.

It is possible now so it should still be.

I need to correct myself, use case 2 above is indeed supported with this model.
This would be achieved by having seperate source records for Proflie 3, which would point to the different scraper, but would reference the same location.

Code:

SCRAPER Table
ID  Name
1   metadata.themoviedb.org
2   some.dutch.scraper

SOURCE Table
ID  Path                                            Name                ScraperID
1   nfs://192.168.0.100/raid0/data/Media/Movies/    Movies (english)    1
2   nfs://192.168.0.100/raid0/data/Media/Movies/    Movies (dutch)      2

PROFILESOURCEACCESS Table
ProfileID   SourceID
1           1
2           1
3           2

[Martijn]

you need to take in account scraper settings too. not only different scrapers but also different language setting / use folder name / dvd order

[DecK]

Martijn
What table/columns are those scraper settign currently stored in?

I would manage these as fields in either the Scraper or Source table, depending on whether those settings are scraper or source specific.