Hi,

Apologies in advance if the following is not well formulated. I'm a beginner who's tried to get a general idea of database design but it appears what I'm trying to do is rather difficult -- or perhaps I haven't enough knowledge.

I'm planning a database for classical music items: catalog of musical works, but also connected in many-many relationships with musical scores (sheet music editions/compilations) on one hand and recordings (CD/LP/digital) on the other (there is a relationship between score edition and recordings too, but I might not get into that). I have some idea how to begin designing this, but what has stumped me is the case of a work consisting of many parts (most typically an opera with many arias/choruses/etc) each of which can be in a many to many relationship with a recording. Is there any way other than to use each separate subcomponent (arias/choruses) as a separate object? The general problem is the tediousness of entering data for every single one of movements in single musical work, ie each track in a recording having its own performer(s) data, the bulk of which would be a duplication of the data for the recording as a whole. The way this is typically handled is just to have a list of performer names for each performance/recording and then depend on contextual knowledge to understand how they fit in vis a vis roles in the musical work, and likewise make assumptions about who sings in which track.

one notion I had is as each movement in a work would have a predefined performer(s) type that would be standard (ie a string quartet would have violin/violin/viola/cello), this could be predefined by work and then pre-populated from a list for each separate performance/recording instance. However, the not uncommon presence of non-standard use of performer types in performances/recordings would make this problematic. Say a Mass setting: five movements, the first for two voices, the second for five voices, etc. But in one particular performance, the first movement has two singers, plus a named instrumentalist, plus unnamed members of an orchestra and a chorus. How to incorporate all this other than a list of names, especially given the ambiguities in available data (ie information we know may not be complete or unambiguous for each instance).

If anyone could give me some pointers towards resources for defining these problems better I'd be most appreciative,

Victor

It would help if you showed what (if anything) you've already tried so we can give you specific recommendations.

However, as a starting point, I'd suggest researching supertype/subtype relationships. You can use this to model Performers, of which there are two types: Groups ("Empire Brass", "London Symphony Orchestra", etc) and Soloists ("Itzhak Perlman", "Yo-Yo Ma", etc). If necessary, you can relate soloists to groups to show an individual's membership in a larger performance group.

You could also use subtypes to model music, in which case you'll have something like a Composition ("Symphony #5", "Cinderella", etc) and a Piece ("1st Movement", "Act 1, No 5: The Fairy Godmother", etc). In this case, a Piece will have a mandatory many-to-one relationship with a Composition.

This assumes a many-to-one relationship between a Piece and a Composition. However, if the relationship is many-to-many, then I would go with one Composition table and have a resolving "Composition Makeup" table. That would allow you to, for example, associate "Dance of the Sugar Plum Fairies" with both "The Nutcracker" and "The Nutcracker Suite", but associate "Battle with the Mouse King" with only "The Nutcracker" (all of which would be listed in the Composition table).

From there, think about how to model a Performance, which is simply one or more performers performing a composition.