Do you have a database of your music? If so

[The Magnificent Goldberg]

I've been using a relational database to keep records of my albums in since the mid eighties. I bought the odd boxed set but there were never enough of them for me to worry about handling them in a special way until I started buying Mosaics in 2001. Now I've got about 40 sets and, as I updated my database for the new year yesterday, decided that I'd think up a way of handling them consistently.

The difficulty is that my database is constructed on the assumption that I can buy many different copies of an album - on different labels (Prestiges on Esquire, Stateside, Transatlantic, Xtra and BGP just in Britain), and mono, stereo LPs, K7s, CDs - but in the end, it's the same album, so I only have one data record for it (but several records representing the purchase of each version).

So along comes, say, the Gerald Wilson Mosaic box, which represents new copies of a number of albums I've purchased (sometimes more than once ) over the previous thirty odd years, plus a few that I didn't have before. Consistent with the rest of the database, I record this box as its constituent albums. But that makes it difficult to see the data records relating to the box as a whole.

I've created a couple of new entitiies and relationships between them and the other tables in the database and pulled all the data together (not without a puzzling error or two ). I've backed it up so that it hasn't overwritten the version I had yesterday. And I thought I'd ask how other people deal with this kind of issue in their own databases.

MG

[seeline]

I used to keep up a database/running inventory of my CDs and LPs - and never found one "right" way of handling boxed sets. For the most part, I simply counted them as a single item, without trying to cross-check against the rest. (Especially true of Mosaic boxes, since they don't include the original cover art and cuts from a single album might be spread out over more than 1 LP/CD .)

[GA Russell]

MG, I started my database in '87 on my new Atari 1040ST, and I still use that computer for that purpose!

My records are inconsistent. All of my cds are listed, sometimes more than once in the case of box sets, and always more than once if I have more than one copy.

When I receive a box set in the mail, I list each cd separately with a Roman numeral for each.

I record the date that I listen to a cd for the first time ("open" it). Sometimes I will listen to an entire cd from a box set, and but often I will listen to only the first portion as it corresponds to the lp. In that case, I will make a second entry for that portion of the cd that I haven't listened to yet.

[rockefeller center]

Edited January 2, 2009 by rockefeller center

[The Magnificent Goldberg]

Can't do diagrams on the PC, but that's more or less what I've got, except that album to artist is a many to many relationship, not a many to one; eg "ESPM the reunion: Live at Akhbar Hall" is by Charles Earland, Mel Sparks, Houston Person & Idris Muhammad. And I have loads of multiple artist albums (I expect we all do).

MG

[rockefeller center]

Can't do diagrams on the PC, but that's more or less what I've got, except that album to artist is a many to many relationship, not a many to one; eg "ESPM the reunion: Live at Akhbar Hall" is by Charles Earland, Mel Sparks, Houston Person & Idris Muhammad. And I have loads of multiple artist albums (I expect we all do).

MG

Album : boxed set is n : m, so I corrected that in my diagram.

I would use the "band/artist" entity just to have a name under which an album was released (there are probably better names to describe this entity). I'd have another m:n relationship between album and artist (and the instruments a musician plays on a given album).

[band/artist]-1-------------<compiles>-------------m-[album]-m-----------------<has>-------------------n-[artist/musician]

I'm still suffering from heavy new year's eve alcohol abuse so my thinking should still be flawed. Wonder if the that leap year bug in Microsoft's Zune player is alcohol related http://blog.tiensivu.com/aaron/archives/17...-one-error.html

Edited January 3, 2009 by rockefeller center

[John L]

The fundamental unit in my database is track rather than album, although I often browse by album. In that case, If I obtain a box set that contains albums, I delete the individual album references in the database, and move all the information about the albums and digital artwork to the box set entry. (If I don 't do that, I will end up with double track entries). That is not a perfect solution. If I look up an album entry and it is contained in a box set, I will turn up nothing. I have to remember that it is on a box set. In case I don't, I can always browse by artist until I find the music that I am looking for.

Edited January 3, 2009 by John L

[gmonahan]

I use an ancient database called "Notebuilder" that allows me to file individual entries in a single record for artist, instrument(s), album title, label, catalog number, recording date, session leader, live or studio, and miscellaneous stuff like whether it's part of a series. I do an individual record for each musician on a given album who solos, and sometimes for those of note who don't. My database lets me create sets by any of these characteristics (all trombonists, every record on a given label, and combinations--all albums featuring a given artist recorded in a given year, for example). Takes kind of a long time to index them all, and I often get behind. I've only got my cds indexed. I started after I'd collected a bunch of LPs and never had the time to go back and do them, and I don't even want to think of the 78s I amassed from various thrift stores when I was a kid!

And yeah, I'm kind of anal about it!

greg mo

[The Magnificent Goldberg]

I use an ancient database called "Notebuilder" that allows me to file individual entries in a single record for artist, instrument(s), album title, label, catalog number, recording date, session leader, live or studio, and miscellaneous stuff like whether it's part of a series. I do an individual record for each musician on a given album who solos, and sometimes for those of note who don't. My database lets me create sets by any of these characteristics (all trombonists, every record on a given label, and combinations--all albums featuring a given artist recorded in a given year, for example). Takes kind of a long time to index them all, and I often get behind. I've only got my cds indexed. I started after I'd collected a bunch of LPs and never had the time to go back and do them, and I don't even want to think of the 78s I amassed from various thrift stores when I was a kid!

And yeah, I'm kind of anal about it!

greg mo

That does sound like an ancient database!

I never bothered recording track titles or personnel - I started off with one on a card index, so that wasn't a useful thing to record in those days because it couldn't be accessed systematically. I'm very slowly creating tables of tracks and personnel, just for the jazz albums - doing the same for African recordings, Gospel, Blues, R&B, Soul, Funk and Hip hop seems to present all kinds of different problems and produce very little in terms of benefits. But it's very boring and I usually find something else to do So I suspect that a smaller proportion of my collection is indexed now than was the case a year ago

MG

[The Magnificent Goldberg]

Can't do diagrams on the PC, but that's more or less what I've got, except that album to artist is a many to many relationship, not a many to one; eg "ESPM the reunion: Live at Akhbar Hall" is by Charles Earland, Mel Sparks, Houston Person & Idris Muhammad. And I have loads of multiple artist albums (I expect we all do).

MG

Album : boxed set is n : m, so I corrected that in my diagram.

I would use the "band/artist" entity just to have a name under which an album was released (there are probably better names to describe this entity). I'd have another m:n relationship between album and artist (and the instruments a musician plays on a given album).

[band/artist]-1-------------<compiles>-------------m-[album]-m-----------------<has>-------------------n-[artist/musician]

I keep a separate table for artists, too, with their nationality and what instrument(s) they mainly play, with a separate table relating artists to albums in the many to many relationship. I'm slowly building album personnel and tracks tables. If a musician plays several instruments on an album (or even track), do you record each instrument in a separate field or lump them all in together in one field?

MG

[rockefeller center]

If a musician plays several instruments on an album (or even track), do you record each instrument in a separate field or lump them all in together in one field?

I'd have a many to many relationship between instrument/musician and track (an album consists of tracks, of course; didn't want to go into such detail because your "problem" seemed to be the boxed set - album relationship). Wouldn't make sense otherwise (database normalization) because it would be a hassle to even do simple queries like (pseudocode follows) SELECT track, artist.first_name, artist.last_name FROM [tables and join bla bla] WHERE instrument = 'kbd'. Is this a test?

[The Magnificent Goldberg]

If a musician plays several instruments on an album (or even track), do you record each instrument in a separate field or lump them all in together in one field?

I'd have a many to many relationship between instrument/musician and track (an album consists of tracks, of course; didn't want to go into such detail because your "problem" seemed to be the boxed set - album relationship). Wouldn't make sense otherwise (database normalization) because it would be a hassle to even do simple queries like (pseudocode follows) SELECT track, artist.first_name, artist.last_name FROM [tables and join bla bla] WHERE instrument = 'kbd'. Is this a test?

Yes - I agree it would be a hassle - and not really produce much of an interesting result. That's why I went for putting all instruments in one field. When I get it finished, if ever, I'll be able to pick out all albums with a trombonist on them, or a named trombonist, and that'll do me.

And yes - I was really more interested in the boxed sets thing, but since you seemed to know a bit about the whole business, I thought I'd derail my own thread

MG

[sidewinder]

MG, I started my database in '87 on my new Atari 1040ST, and I still use that computer for that purpose!

Holy jeez ! I sold mine over 20 years ago. That machine was great fun though. Heck, you could even get Modula-2 and ADA compilers for it !

Edited January 3, 2009 by sidewinder

[rockefeller center]

If a musician plays several instruments on an album (or even track), do you record each instrument in a separate field or lump them all in together in one field?

I'd have a many to many relationship between instrument/musician and track (an album consists of tracks, of course; didn't want to go into such detail because your "problem" seemed to be the boxed set - album relationship). Wouldn't make sense otherwise (database normalization) because it would be a hassle to even do simple queries like (pseudocode follows) SELECT track, artist.first_name, artist.last_name FROM [tables and join bla bla] WHERE instrument = 'kbd'. Is this a test?

Yes - I agree it would be a hassle - and not really produce much of an interesting result. That's why I went for putting all instruments in one field. When I get it finished, if ever, I'll be able to pick out all albums with a trombonist on them, or a named trombonist, and that'll do me.

Actually I meant the opposite. If I'd care to design a database for my music collection (I don't because I'm too lazy to enter data) I would not put all instruments in one field but have a record for each instrument a musician plays on a track. If significant performance backdraws aren't to be expected I go for higher granularity. One never knows what kind of queries Rooster Ties comes up with (like "on how many different instruments do you have artist x on your records where the instrument is not a piano?").

Edited January 3, 2009 by rockefeller center

[The Magnificent Goldberg]

One never knows what kind of queries Rooster Ties comes up with (like "on how many different instruments do you have artist x on your records where the instrument is not a piano?").

I've got an answer to that one!

MG

[randyhersom]

Boxes like Proper sets usually overlap LPs rather than containing them whole.

track

leader

sideman

release

tracks have one or more leaders, zero or more sidemen and one or more releases. This allows you look up Much More under either Marion Brown or Mal Waldron. A few linking tables do the trick.

TrackID, ReleaseID

TrackID, MusicianID, Role, (optional) Doubles_on

If you wanted to know the instrument played on each track, you could establish a primary instrument in the Musicians table and only fill out the doubles_on when some other instrument was played on the track. I'm not feeling anywhere near that ambitious, myself.

A wish list item for Microsoft. It would be nice if Media Player could play shortcuts as if they were MP3s. Then you could create shortcut directories for the original albums contained in a burned box set and easily choose to listen to the set or the original album as suits you. Alas, my Media player won't play my shortcuts.