Proposed change to the way we catalogue tracks

[Gigablah]

The purpose of this thread is to suggest a change to the current database structure which will allow for more flexible cataloging of game music, and to invite comments or additional suggestions.

I'm going to make this short and simple, so here's a diagram:



(the internal boxes represent examples of table entries and are not meant to be exhaustive)

Essentially, two additional tables will be inserted between tracklists and albums; one for individual tracks and one for media.

Rationale for separating tracks and tracklists:

• The tracklength field is repeated across different tracklists. This is not necessary, plus we only take the tracklengths from the default tracklist anyway.
• To ensure that each unique track gets one single entry. We're going to expand upon this aspect in the future with full pages for (selected) individual tracks and inter-track relationships... and track-specific artist credits.
• Partial translation capability. There's no need to reproduce a track title that's originally in English.

Rationale for the media table:

• Albums can have different media types packaged together (for example, CD + DVD). There's currently no way to specify this.
• Media can be directly associated with products (such as game releases) instead of albums. This means game discs with redbook audio can be registered with their own tracklists.
• Media can have individual catalogue numbers and titles. This can be helpful for searches.

This comes with the implication that certain enclosures which were packaged as part of a game (such as the second disc of a special edition DVD case) should now be directly associated with a product entry rather than an album entry. The album listing display will also be affected (supplemented by non-album media entries). I hope to implement this in such a way that the current album submission procedure will remain largely unchanged.

Questions and suggestions are welcome!

[Secret Squirrel]

We already have a track table, don't we? It sounds like you're talking about some reorganization though, where instead of having the English and Japanese tracks as separate entries, they'd both be attributes of a single track. That would definitely nice, since the time would only need to be stored once.

Media is an interesting idea. I could envision that we'll have media types such as N64 Cart or 3.5" Floppy for some games. I hope that there'll be some kind of dual association so that certain enclosures can be listed and searchable in the album database while retaining their association with the product database.

As for redbooks, this seems like a good way to use the tools available to store information about redbook discs. However, I still don't want to see redbooks in the albums database. If we are going to catalogue music on redbooks, we need to expand the design so that we can handle music information from other formats -- midi, spc, digital music stored in the game data files, etc. In other words, we'd be covering the music from every game in every format, along with the composer associations and all the new metadata types. One nice thing that would fall out of this is that we could effectively provide a recipe for extracting the music from any game.

[Gigablah]

With this design, redbooks won't be in the album database at all, since they'll be associated with product entries rather than album entries. It's a multi-tier design -- the track database can store any kind of music information whether it's contained in game data or on physical media; the media table organizes a subset of tracks into an ordered list, and both the product and album tables further organize subsets of media.

In fact, you could probably directly associate tracks with products, skipping media and albums entirely. There's the possibility of creating yet another table between tracks and products in order to organize such "free-floating" music info into something more coherent.

[Eriol]

I was not around when this database was designed. Is there a product table in this database? Does that mean somebody could search on a game disc and get any associated game music enclosures?

Otherwise, your proposal seems like a very normalized database and sounds good for the type of cataloging you are planning to do.

[Gigablah]

We haven't implemented series and products yet, but yeah, eventually you can search for a particular game and see a list of enclosures associated with its various releases, etc.

[Kaleb.G]

Originally Posted by Gigablah

stuff in bold

*everyone claps*

Originally Posted by Gigablah

This comes with the implication that certain enclosures which were packaged as part of a game (such as the second disc of a special edition DVD case) should now be directly associated with a product entry rather than an album entry. The album listing display will also be affected (supplemented by non-album media entries). I hope to implement this in such a way that the current album submission procedure will remain largely unchanged.

As long as we can still see enclosures/promos included on the site just as we currently do, I approve of all of these changes.

Originally Posted by Gigablah

In fact, you could probably directly associate tracks with products, skipping media and albums entirely. There's the possibility of creating yet another table between tracks and products in order to organize such "free-floating" music info into something more coherent.

That seems like it would be an extremely rare case. A product that has no media but has music? Surely you could just put a media of type "other".