firnsy Wrote:I am growing on this suggestion of having dedicated *collection and *content tables ensures we can optimise the metadata to content and still have the tables coexist under one DB. My only reservation is that it breaks is the intercontent relations such as Soundtrack to Movie which is to be provided by contentlinkcontent.
Not necessarily. See last paragraph.
firnsy Wrote:What is the likelihood of mixed content collections (ie collection consisting of both music and video)? This follows on from the aforementioned issue of intercontent relations and the most efficient storage method for these links.
Not sure if it is feasible on the ui side, but for example I would surely like to have grouped together the photos and the video clips from my camera.
firnsy Wrote:Is there a need for nested collections? Examples?
TVSeries > Season > Episodes.
"TVSeries" collection has nested multiple "Season" collections.
Also similarly in music you can have an Album (or BoxSet) > Discs > Tracks.
If I analyze it correctly, there are two types of collections, let me name them as Logical and Natural.
The Logical collections are user defined as you described them in classifications table. They don't hold much of metadata, apart from a title and a description maybe. They can contain any content or collection even of mixed type (like photos and videos). Movie Sets can fit here as well.
The Natural (or structural/technical?) ones are natural content collections based on the content type like Music Albums, TvSeries, etc. These can have quite rich metadata, they can be nested as well, but should be restricted to only one content type.
So I suggest that the classification table to be used only for the user defined collections and that we define separate content and collection tables for each content type. Each table (also the collection ones) has a contentId as primary key that is unique over the whole content and collection tables; it is derived from a global sequence. The parentId on each table will be a reference to a contentId. The contentlinkcontent table can be used for inter-content relations as it is now, with the addition of a "targettable" field which will hold the name of the table that contains the target contentId (not sure that this is optimal though). EDIT: Similar change of course has to be applied in classificationlinkcontent table.