Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    4

    Database Design for Classical Music database

    Hello, I'm designing a database (what will be freeware) to store information from classical music CD's. I have (what I think is a small) problem on the design of it. I have a MS-Access designer screenshot of the problem and another screenshot of my proposed solution. I also have a quote from an online friend (which I'm no longer in touch with) as to the problem and the proposed solution in text.

    I was wondering what would be the best way to ask the design question. Can I post the two screenshots as well as his explanation? Thanks.

    EDIT: Just to be clear, I'm only doing the design in Access but will convert the database to SQL Server when the design is done.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Sure, you can post the images and the text here at DBForums.

    As a new user you may have problems posting them as URLs due to the technical hurdles that iNet has used to limit the never-ending assault of the spammers.

    Go ahead and try to post what you want. If you run into troubles, contact me or any of the other staff (our user names appear in gold, red, or blue) and we'll be glad to help!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2014
    Posts
    4
    Here is the 'Before' and 'After'. I did both of the "designs". The second version is the one that I did based on the suggestion that I got. As I interpret it, the second version has a table(RelationMaster) that has really nothing but primary keys. Then those are used with the foreign keys in each table to retrieve the fields in those tables that I want. Am I interpreting this correctly? Here is the description that my friend wrote of the problem.

    If I may, the one spot that I see you may run into problems is on CDs where there is more than one featured performer (For example, Mahler 8 with all the soloists, which is what it seems you are looking to include?). Since your CD table (the table on the right) is a "top-level" table, and has the ID (Primary Key, in the parlance) field, if you add more than one performer, each CD is going to wind up with more than one ID.

    I would make a table on the left with the CD information so that each CD has one ID number, then you can have a table on the right that ties together all the various IDs from the tables on the left. Does that make any sense at all?


    Before: Attachment 16087 After: Attachment 16088

    EDIT:

    I thought I would try to illustrate the problem more clearly. Let's say that I have a rock CD. I have a 'CD table' that has a field called 'Performers'. The rock band has 3 guitarists and I want to capture all of them. I can put those performers in a seperate table with each guitarist having a name and unique ID. The problem is that in the CD table I only have one 'Performers' field. In classical music this problem is exacerbated because of the nature of the music.
    Last edited by ntman12; 01-12-15 at 09:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •