2013-02-08, 01:39
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 ;
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.
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
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)
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