On 19 Aug 2008 at 20:35, Christopher Smith wrote: > On Aug 19, 2008, at 8:09 PM, David W. Fenton wrote: > > > > But a flat file approach is guaranteed to produce far more problems > > in the long run than are possible with a relational structure, unless > > that, too, has been improperly designed. > > You seem to have a lot of experience here. Assuming I'm running a > school music performance materials library of 500-700 pieces or so. > > How would you suggest setting up the fields in a database (like > Filemaker) for instrumentation?
Well, there are two issues raised by Johannes: 1. instrumentation/number of parts. 2. who has what part. Actually, that's probably *three* things, but I'll address instrumentation first (though the solution to both can use the same basic data structure). If you're not doing #2 (i.e., tracking the location of each individual part), it would be accomplished with a many-to-many join table that would map the relationship between a piece of music and the instruments. It's hard for me to figure out the right term for the entity represented by each piece in the library. Work? Nope, since you can have more than one edition of the work. Score? Nope, since that's misleading in that you will likely have both score and parts. Piece works, but also edition (particularly if you have more than one edition of some pieces). Let's assume our table for each piece of music is called tblPiece. Each piece will have a PieceID. Instruments is easier -- you have a table of instruments (and, likely, abbreviations), called tblInstrument, and each instrument has an InstrumentID. The join table connecting tblPiece<->tblinstrument would have these fields and would be called tblPieceInstrument: PieceID InstrumentID You would put a composite unique index on the combined pair of fields, so you couldn't have the same instrument twice in tblPieceInstrument for the same piece. Now, if you wanted to track number of parts, you'd add a field to tblPieceInstrument: *PieceID *InstrumentID Parts Now, how to summarize this? Well, that's going to be specific to each database application development environment you're using. The easiest way is to just list the records in tblPiece. If, however, you want a printout of your pieces with instrumentation, you're likely going to want something like: Instrumentation: score (1), v1 (6), v2 (6), vl (5), vc (4), cb (3) Getting that from the records in the join table may require some programming, that will look up all the records for that piece and then concatenate this list, but how to implement that will depend on the application building tools you're using. Now, if you want to track individual parts, there are two possible ways to model it -- either by putting more data into tblPieceInstrument or by creating tblParts, which would be linked to tblPieceInstrument. For the former, it would like something like this: **PieceID **InstrumentID *PartID The indexing would be a non-unique index on PieceID/InstrumentID and a unique index on PartID. To get the list of instrumentation, you'd have to summarize this table so that you first got: PieceID InstrumentID Count(PartID) Then you'd concatenate that the same way you did the table with field that had the number of parts in it. Now, the other way to do this would be to put the record for each part in a different table, and tblPieceInstrument would be just this: *PieceID *InstrumentID The parts table, tblPart would be: *PartID PieceID InstrumentID This is a more proper structure, but I'm not sure it offers any advantages over putting the same information in the join table, except in making the indexes somewhat more sensible. > Would you say something like one field for instrumentation, and then > fill that field with the actual instruments? I'd never do that. In fact, that's what I tried back in the day with my piano quartets database and it caused all sorts of problems. > Or with the name of the > ensemble if it is a standard one? Or would you have a Piccolo field, > with yes or no or a number of parts, then on to the Flute 1, etc.? > > Or am I still thinking flat file? (our library is presently an Excel > spreadsheet, and I tend to think that way!) Yes, you're thinking flat file. Any time you have repeating columns that represent the same entity, you're "committing spreadsheet," which is a bad thing, since it makes it hard to summarize and query the data. > Our library contains concert band, jazz band, combo, and chamber > music in various combinations. Obviously for it to be more useful > than the present spreadsheet, it would have to be browseable by > instrumentation. How would that work with slightly differing > instrumentations (like a lot of concert band pieces?) You'd likely want a genre field in addition to the instrumentation join table that I describe above. That can lead to other problems, of course (such as chamber music with variable instrumentation), but that only matters if the problem is a problem, so to speak. That is, the variable instrumentation causes issues only if you need to make distinctions based on it. As I said before, cataloging music is a complex issue. For instance, nobody has mentioned the problem of how you handle: 1. multiple editions of the same piece 2. arrangements of the same piece 3. uniform titles 4. collections of multiple pieces in the same edition 5. collections of multiple composers in the same edition If you have these issues and need to track them properly and enforce regularity of data entry, then it becomes pretty tough pretty quickly. I had all those problems in my piano quartets database! And I was originally working in a database that lacked two really important features: 1. engine-level referential integrity enforcement 2. more than two nested levels of relationships displayable onscreen at a time. This meant that I had to do all sorts of really bad things to make it work! And that's why I so strongly appreciate modern database engines and modern development tools! -- David W. Fenton http://dfenton.com David Fenton Associates http://dfenton.com/DFA/ _______________________________________________ Finale mailing list [email protected] http://lists.shsu.edu/mailman/listinfo/finale
