Help setting up a confusing karaoke/music db

[CuriousGeo]

I'm helping a guy who runs Karaoke shows redo his database of music since his old system (Lotus database which he made himself is way past its lifecycle). I looked at his Lotus database (and I say that loosely-it was bascially a big table with everything in it-it looked like a spreadsheet) which we extracted as a spreadsheet to rebuild his new one.

It has multiple descriptive fields which each one describing an attribute of an artist and song combination. His original dataset is basically a huge Excel spreadsheet containing over 118,000 lines/records of different artist-song combinations with all these field used to describe quality, location etc.
His system sounds overly complicated and confusing to me, but to him it makes perfect sense since he came up with it...

To make him happy, he wants to include every field he has been using these past 10 years in a new Access Database.
He has multiple copies of the same Artist Song combination, but each one is unique depending on all his other tracking fields

Items needed to track are:
-Artist
-Song (each artist can have multiple songs)
-Volume Field 3000 choices (which Volume the song comes from)
-Tag Field, choices 1 thru 8 (used to describe the sound quality)
-CDTrack Field, choices 1 thru 100 (CD track #)
-LDTrack Field, choices 1 thru 15 (Laserdisc Track #)
-QR Field, choices 1 thru 100 (another quality indicator)
-Exclude Field, 6 choices (used to categorize types of songs such as Spanish, Gospel, Childrens, Explicit lyrics, etc. so that he can exclude them from different venues where he plays)
-CR1 Field (don't know what these are for but there are 1-20 choices)
-CR2 Field (don't know what these are for but there are 1-20 choices)
-CR3 Field (don't know what these are for but there are 1-20 choices)
What tables do I need to make and what would the relationships be?
Here is a sample of 100 lines from his spreadsheet. Any help in setting this up would be appreciated.

 

[namliam]

Basicaly just follow the steps of normalization I would say...

Sounds to me like you just need 2 tables, Artist and Song...
Then a table for CD/Track information

On the other hand if he is used to this and doesnt mind the additional work of re-entering the artist when needed, etc, then why not simply keep his "excel sheet" in tact, perhaps even in excel instead of access......

 

[CuriousGeo]

Thanks for the reply, I have normalized and pulled out the original data into separate tables (Artist, LD, CD, QR, Tag, CR1, and so on). I was looking for an easier way for him to enter new music or make changes to existing entries. The spreadsheet entry method worked for him, but it is a huge spreadsheet, like I said over 118000 lines. It's cumbersome to work that way, hunting for the record you want.

He also had to make multiple entries of the same song/artist combo because he had up to 10-15 different copies of it with different attributes (the list of fields I listed in the first post), and all stored on different computers/karaoke machines.

 

[CuriousGeo]

OK, have been searching and found Allen Browne's tips, I was thinking about Yes/No Checkboxes-then found this:
Don't use Yes/No fields to store preferences ( allenbrowne.com/casu-23.html )

This would work for what I want to do, but I'm not sure what tables I need to relate to one another. His example shows students with multiple sports attached to them. This is the same as what I want to happen in the Karaoke db-each artist-song combination would have multiple "tags" (the quality indicators I listed in my original post-i.e. LDTrack, CDTrack, QR, CR1, etc.) What tables would I need to set up and relate to one another?

His example sets up 3 tables:

• Student table (one record per student), with fields:
  - StudentID AutoNumber
  - Surname Text
  - FirstName Text
• Sport table (one record per sport), with fields:
  - SportID AutoNumber
  - Sport Text
• StudentSport table (one record per preference) with fields:
  - StudentSportID AutoNumber
  - StudentID Number Relates to Student.StudentID
  - SportID Number Relates to Sport.SportID

 

[jdraw]

Here's a tutorial that will lead you through the steps -- from a business overview to designed tables and relationships. Well worth the effort to work through the tutorial.

Good luck.

 

[CuriousGeo]

Thank you jdraw, looking at that tutorial refreshed my memory and helped me to set up what I hope will work-it appears that way....

If anyone is interested here is what I did with a screenshot of my Record Entry Form.

I made a table of Artist & Song, made another table with all the attributes that would be associated with each Artist Song combination. I turned that attribute table into a subform and linked it in my entry form by the ArtistSongID field

Now as you scroll through each record, all the various attributes show up for each Artist Song combination.

When the karaoke operator edits an entry, he can change any of the attributes as needed via comboboxes instead of retyping entries like he was doing in his old spreadsheet database version.