Database schema for file organizer.

[Master_Jedi25]

Project idea

Database schema

I would like the content of the database to be easily imported and exported to be used with calibre, stash, stash-box, beets. I want to save as much information from those databases as possible, and then do an ETL to use the same information with those programs. But at the same time I wouldn’t want to make the database too complicated. So I don’t know if I should add all information from books, movies, music, etc to the `file` entity or if I should make them its own entity. The same for albums, tv shows, etc and `collection`. Are there any other programs I should look at?

There are many different versions for each file that may vary in very little details, for example a movie may have different resolutions, but I want the ratings, comments, etc to refer to a single movie while at the same time being able to specify a version, to distinguish for example between the first and second version of a textbook. How can I express this in the database?

I want users to be able to react to and rate pretty much anything, does this mean I have to create a new table for each kind of reaction? For tag, person, collection, etc?

Since it's a collaborative, crowd-sourced database I need a history of edits for each modification but I don't know how to express that.

 

[AngleWyrm]

I need a history of edits for each modification but I don't know how to express that.

Add each change to a document as a new record in a collection that represents the history of that document.
Primary key is a time stamp.