New Database Question

[Brewmeister]

All
I am in the process of creating a database for my music collection. This will consist of both officially released music (LPs or CDs) as well as "unofficially" released live concerts (taper friendly bands like the Grateful Dead) from groups that allow you to record and trade their concerts.

So far this is what I have put together for the table structure

TBLRecording - Artist (fk); Album Title; Year Released; Label (fk) Cataloge Number (PK?); Genre (fk); Tracks (fk).
TBLArtist - Artist (not sure if this should be left as a natural key or a alternate key - AutoNumber)
TBLFormat - Format (same as TBLArtist above on the Key part) - CD, LP, etc
TBLMusicLabel - Music Label (as above for Key) Sony, BMG, etc
TBLGenre - Genre (as above for Key) Blues, Rock, Classical etc
TBL Venue - Venue (as above for Key) Fillmore East, Fillmore West, etc
TBLConcert - Concert Title; Venue (fk); Date Performed; Format (fk); Data Type; Converted to Wav (check box); Tracks (fk)
TBLTracks - TrackID; Track Name
TBLDataType - Data Type (unsure about key type here either) SHN, FLAC, etc

I am not sure if I should have a seperate table for the Year Released (since more than one album is released per year, I don't know if this would create more headaches than it is worth)

After the table format is complete, I will work on improving the relationships, I know I will have a few Many to Many relationships, but am not sure if that is a good thing. Some of the Many to Many relationships could be Artist to Genre (i.e Eric Clapton - rock, blues), Artist to Track (definite) How many bands have covered Good Lovin'??, Artist to Venue, the Grateful Dead were regulars at the Fillmore.

I suppose the bottom portion realy belongs in the releationship thread, I just threw it out here for continuity.

David W Reece

 

[The_Doc_Man]

When I did this, I had these tables:

Discs - representing the disk and showing its publisher's info - like the label name (SONY, Verve, Dot, Decca, Columbia, etc.), serial number from the publisher's catalog (everyone's number sequence is different, of course...), a code for album genre, the number of tracks, the total length of the tracks when combined, disc title, and a number I added for my own records.

The "genre code" was a lookup to a "genre" table that listed the genres I had defined. At this point I have about forty, some of which are fine-grained divisions. The genre code shows TWO parts - a broad-brush part and a specific part. So, for example, I have "Foreign" as a broad-brush genre and specific country names as the fine-grained variants. I have "Rock" as the broad-brush and "Country", "Acid", "Light", "Hard", etc. I have "Classical" as the broad-brush and "Baroque", "Renaissance", "Romantic", "Neo", "Modern", etc. Genre "Country" becomes "Ballad", "Western", "Howlin' Dawg", "Bluegrass", ... I think you've got the idea.

Attributes - representing things that required more knowledge about the disk. Here, I had data-records that included selection number, selection name, selection artist, group, etc. I had as many attributes as I wanted. One of the attributes was even "cover art" - though in retrospect I should have made that part of the Discs table. Oh, well, nobody's perfect. But as a space-saver, it actually is the right decision, since I didn't record cover art for every album! So I saved space for any cover-art slot that would have been in the main CD table.

Instead of having separate fields for each attribute, I named the attribute according to an attribute code, so all I really stored for each attribute was a number, a type-code, and a text value. Then I had a lookup for the possible attribute codes.

The way I worked the codes was that if I left the attribute number at 0, it applied to the whole disc. BUT if I put in a number, it only applied to the given track number. That way, anthology albums (like "Chart Toppers" or "Best of the 80's") could have individual artists named per track.

Then, when I wanted to find out the music titles, I searched the attributes for all records with the code for track titles only. When I wanted to find all music by, say, the Amboy Dukes, I searched for all records with that text and attribute code "Band or Group." Since I have lots of classical anthologies, I included codes for orchestra, chorus (often different from orchestra), conductor, choir director, featured instrument (where applicable), composer, lyricist, .... I might not have stored data for every CD for every category, but I retained the flexibility.

The whole thing takes four tables:

tblCD (parent table of the whole schmier)
tblAttribute (child of tblCD, linked to tblCD's PK, which was an autonumber)
tblCDGenre (lookup-only for code stored in tblCD) - three fields.
tblAttrName (lookup-only for code stored in tblAttribute)

Reports weren't so hard, either, once you build the query that does the lookups in a JOIN, tblCD to tblCDGenre, then the query that does the JOIN, qryCDGen to tblCDAttribute, then the final query that does the JOIN, qryCDGenAttr to tblAttrName. Then group by CD and by Attribute code and print whatever ya got as detail lines.

Or query with filters and order-by to find all classicals, all country-western, all zydeco (popular in S. Louisiana), all Broadway show tunes, all Rock'n'Roll, all R&B, etc etc etc. The sky's the limit. And I can add attributes after-the-fact if necessary. 'cause it's just a lookup table entry to define a totally new attribute. AND, if you decided that you wanted to track something special about the recording, you can add the attribute and then store the data. Like, for your case, where you have some "live-recorded" performances, add "DateRecorded", "LocationRecorded", etc.

Just one man's shot at doing things. Give it some thought.

 

[Brewmeister]

When I did this, I had these tables:

Discs - representing the disk and showing its publisher's info - like the label name (SONY, Verve, Dot, Decca, Columbia, etc.), serial number from the publisher's catalog (everyone's number sequence is different, of course...), a code for album genre, the number of tracks, the total length of the tracks when combined, disc title, and a number I added for my own records.

The "genre code" was a lookup to a "genre" table that listed the genres I had defined. At this point I have about forty, some of which are fine-grained divisions. The genre code shows TWO parts - a broad-brush part and a specific part. So, for example, I have "Foreign" as a broad-brush genre and specific country names as the fine-grained variants. I have "Rock" as the broad-brush and "Country", "Acid", "Light", "Hard", etc. I have "Classical" as the broad-brush and "Baroque", "Renaissance", "Romantic", "Neo", "Modern", etc. Genre "Country" becomes "Ballad", "Western", "Howlin' Dawg", "Bluegrass", ... I think you've got the idea.

Attributes - representing things that required more knowledge about the disk. Here, I had data-records that included selection number, selection name, selection artist, group, etc. I had as many attributes as I wanted. One of the attributes was even "cover art" - though in retrospect I should have made that part of the Discs table. Oh, well, nobody's perfect. But as a space-saver, it actually is the right decision, since I didn't record cover art for every album! So I saved space for any cover-art slot that would have been in the main CD table.

Instead of having separate fields for each attribute, I named the attribute according to an attribute code, so all I really stored for each attribute was a number, a type-code, and a text value. Then I had a lookup for the possible attribute codes.

The way I worked the codes was that if I left the attribute number at 0, it applied to the whole disc. BUT if I put in a number, it only applied to the given track number. That way, anthology albums (like "Chart Toppers" or "Best of the 80's") could have individual artists named per track.

Then, when I wanted to find out the music titles, I searched the attributes for all records with the code for track titles only. When I wanted to find all music by, say, the Amboy Dukes, I searched for all records with that text and attribute code "Band or Group." Since I have lots of classical anthologies, I included codes for orchestra, chorus (often different from orchestra), conductor, choir director, featured instrument (where applicable), composer, lyricist, .... I might not have stored data for every CD for every category, but I retained the flexibility.

The whole thing takes four tables:

tblCD (parent table of the whole schmier)
tblAttribute (child of tblCD, linked to tblCD's PK, which was an autonumber)
tblCDGenre (lookup-only for code stored in tblCD) - three fields.
tblAttrName (lookup-only for code stored in tblAttribute)

Reports weren't so hard, either, once you build the query that does the lookups in a JOIN, tblCD to tblCDGenre, then the query that does the JOIN, qryCDGen to tblCDAttribute, then the final query that does the JOIN, qryCDGenAttr to tblAttrName. Then group by CD and by Attribute code and print whatever ya got as detail lines.

Or query with filters and order-by to find all classicals, all country-western, all zydeco (popular in S. Louisiana), all Broadway show tunes, all Rock'n'Roll, all R&B, etc etc etc. The sky's the limit. And I can add attributes after-the-fact if necessary. 'cause it's just a lookup table entry to define a totally new attribute. AND, if you decided that you wanted to track something special about the recording, you can add the attribute and then store the data. Like, for your case, where you have some "live-recorded" performances, add "DateRecorded", "LocationRecorded", etc.

Just one man's shot at doing things. Give it some thought.

Doc I think I understand where you went with your tables. I don't quite know (or see perhaps is better) where you put the song titles (is it in tblCD or tbl Attribute??).

I like the idea of the attributes, If I understand it correctly, I could use it to state the type of media (LP, CD, Cass, VHS etc) as well as the format (SHN, FLAC, MP3, etc) and you also stored the Artist information there. This would eliminate a couple of tables which should reduce the potential for errors as queries, forms, and reports are generated.

I am not sure if this would work in my case, as I want to be able to have the individual song titles (TBLTracks) availabe for all artists (think cover songs).

The song list would be a many to many relationship, Since many songs could go on many albums (i.e Queen Bohemian Rahpsoday, and Bicycle Race are on their respective original releases, the greatest hits packages 1, 2, & 3; as well as on the live in Wembly, plus I am sure they will be on the Queen and Paul Rodger get together)

The same would go for the live shows. Lots of bands played the Fillmore West, and they played this venue many times.

I guess it also comes down to are the Primary Key fields set properly?? If I go with a Attribute table then I would have to have a seperate key (auto number or so) and if I keep everything seperate the way it is currently, would the key fields as listed be sufficient or would I need to add another field for them??

Thanks again

the brewmeister

 

[The_Doc_Man]

The CD's title goes in tblCD. The title of a track is called a "Selection" and is in the tblAttr. I was thinking jukeboxes when I used the word "Selection" to mean track title or song title. Also, some of the tracks weren't songs, and I was in my "too damned picky to live" phase.

 

[The_Doc_Man]

As to the problem of finding who was the artist on a "cover" you need a special self-referential relationship, joining the attributes on CD number AND on track number for the SELECTION on one side and the ARTIST on the other side.

 

[Brewmeister]

Doc
Thanks for clearing that up.
I will play with the info you gave me and see what I can come up with