My approach to a music database has been influenced by the fact that much of
my collection is stored in three Sony 200 disc changers which run under the
control of my PC via third party hardware namely Slink-e.
To some, this will be overkill though I feel it contains some compromises
for the sake of simplicity. I have at least covered some territory in
dissecting the problem. I'm presenting some of this in outline form for the
group's contemplation, discussion, ridicule or whatever:
I have named the scheme that I am working with "MuRuPu" (pronounced
moo-roo-poo).
A Mu is a musical composition in the most abstract sense. The set of all
music, performed or not, written down or not, past present or future is the
Universal Mu. All other music is some subset of the Universal Mu. In
practice Mus have titles, composers, lyrics, time and places of composition,
subparts (which are also Mus) etc.
A Ru is a recorded instance of a Mu. A Ru is associated with: A Mu,
performer(s), time and place of recording etc. A Pu may be associated with
more than one Ru. A Ru is not dependent on any particular storage medium
such as CD or tape. A Ru represents the "audio signal or data" independent
of medium.
A Pu refers to the actual physical storage of a Ru, which may be a track on
an actual compact disc, an MP3 file, a 7" or anything else. A Ru may be
represented by more than one Pu.
An "album" is a collection of Rus (recorded tracks) not Mus or Pus. Albums
are also associated with recording labels, dates of issue, cover art etc.
In my scheme, albums are abstract in the sense that instances of an album
can exist on CD, LP, audiocassette etc.
Some tables needed to implement this scheme with basic fields:
Table | Fields
--------------------
tblMus:
MuID
Title
Composer (ArtistID)
MajorPart
MinorPart
tblRus:
RuID
Performer (ArtistID)
PlayingTime (in seconds?)
DateOfRecording
tblPus
TypeOfMedium (CD, MP3…)
MediumID (calculated ID code in the case of CDs)
LocationOnMedium (i.e track number)
RuID
tblArtists
ArtistID
GroupY/N (group or individual ?) (not really necessary, could be
calculated)
FirstName
LastName
NormallyReferedToAsName
ArtistURL (home page)
MajorPart
MinorPart
tblAlbums
AlbumID
AlbumTitle
AlbumArtist (ArtistID)
RecordLabelID
NumberOfTracks (not really necessary, could be calculated)
tblRuAlbums
AlbumID
RuID