Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    7

    Unanswered: Music Collection relationships?

    Hello all,

    I'm working on a new music collection DB and I seem to be stuck with some issues. First of all, I did look at the "Music Collection Database" available at Microsoft and it seems to me that the approach taken of that DB is of an "Album's" perspective.

    For instance, data is recorded on a "per-album" basis and as such, when scrolling through the listing, you can see the same Artist several times with different Albums.

    I, on the other hand, would like it to be from a different perspective.

    • Enter each Artist only once
    • Enter each Song only once (Although one song may also appear on many albums by the same artist, "Greatest Hits", for example.)
    • Enter each Album only once
    • Be able to enter a song or artist without any other info (sometimes you hear a song, but don't know the artist)
    • One song, many artists ("How Do I Live" performed by Trisha Yearwood & LeAnn Rimes)
    • Be able to search for:
    • All Artists
    • All Songs
    • Songs by Artist
    • Songs on Album
    • Songs on Charts (Top 100, etc.)
    • Songs by Genre
    • Artists by Genre
    • etc.
  2. Be able to enter a song or artist without any other info (sometimes you hear a song, but don't know the artist, e.g. Braveheart Theme)
  3. One song, many artists ("How Do I Live" performed by Trisha Yearwood & LeAnn Rimes)
  4. The list of criteria goes on & on, but I think you get the point


  5. I've attached a copy of the DB, which is very basic at this point because I can't seem to figure out how the relationships should be placed to achieve the above listed criteria.

    Also, do I need a separate "Charts" table to record which songs are on a listing, for instance "Top 100 Country Songs"?

    Any information or assistance is greatly appreciated,
    Thanx for now,
    Cheers,
    ziggy1971
Attached Files Attached Files
Reply With Quote Reply With Quote

  • #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's a Many to Many problem. Here is how it goes:

    1. Create as many basic tables as they are criteria you need for performing your searches (Sonds, Artists, Albums, Genres, etc.).
    Each table will have the following structure:

    SysCounter, Autonumber, Primary key
    Name, Text (n), Indexed, Duplicates ok
    xyz... ' any other info you want to associate.

    Ex. Table Songs
    ----------------
    SysCounter, Autonumber, Primary key
    Name, Text 100, Indexed Duplicates ok
    Date_Of_Creation, Date/Time
    etc...

    2. Create a table of Associations having also a primary key (ex. SysCounter) and as many columns of they are tables in 1. Each column being of type Number Long, Indexed, Duplicates ok.

    note: FK stands for Foreign Key i.e., some data, (here a Long Integer) that is a Primary Key in another table.

    Ex. Table Associations
    ---------------------
    SysCounter, AutoNumber, Primary key
    FK_Songs, Number Long, Indexed Duplicates ok
    FK_Performers, Number Long, Indexed Duplicates ok
    FK Albums, Number Long, Indexed Duplicates ok
    FK_Genres, Number Long, Indexed Duplicates ok
    FK_Authors, Number Long, Indexed Duplicates ok
    etc...

    Now you can build a serie of queries that from a basic table will retrieve all pertinent information using the Association table, like this:

    Let's suppose that I start from the Songs table.

    1. I need all the primary keys of the Table Associations where the row I"m busy with is present in the FK_Songs column, so:
    Code:
    SELECT Associations.SysCounter
    FROM Associations
    WHERE (((Associations.FK_Songs)=<current Songs.SysCounter>));
    2. From there, I will retrieve every primary key of every table which is linked with the current row in the Songs table:
    Code:
    SELECT Associations.FK_Albums
    FROM Associations
    WHERE (((Associations.SysCounter)=<every SysCounter from the list I retrieved in 1.>));
    or:
    Code:
    SELECT Associations.FK_Performers
    FROM Associations
    WHERE (((Associations.SysCounter)=<one SysCounter from the list I retrieved in 1.>));
    3. Now I can revieve all the rows linked with my song:
    Code:
    SELECT Albums.*
    FROM Albums
    WHERE (((Albums.SysCounter)=<every FK_Albums from the list I retrieved in 2.>));
    The same principle is valid whatever can be the table you start with (the "root" table).

    Those queries can be dynamically built and become the data source of a listbox, a combobox, a subform etc.

    Have a nice day!

  • #3
    Join Date
    May 2006
    Posts
    7
    Hello all,
    I've looked at the information provided and tried to apply the recommendations as well as I could. However, I have no idea what was being said in the parts about the queries, sorry.

    I've attached an updated copy of the DB with the changes, I hope they're the right ones, but it still just doesn't seem right to me or maybe I'm just missing something here.

    Perhaps, to elaborate on what I would like to store in a DB, a partial list of my music videos is also attached. Please keep in mind though that this is only a part of my vidoe list, I want to add all my mp3's, CD's, etc. also.

    Once again, thanx for any assistance,
    Cheers,
    ziggy1971
    Attached Files Attached Files

  • #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    ziggy,

    Have a look at the attached database, specially at the Qry_Octopus query (you'll quickly understand why I named it that) and at the two forms: Frm_Octopus and specially at Frm_MultiList that demonstrates what can be obtained with almost no programmation.

    With some more code many interesting things can be done with this kind of data model.

    I can't add any other example right now because I'm rather busy at the moment, but I could come back to you later, should you have any more questions.

    Have a nice day!
    Attached Thumbnails Attached Thumbnails Qry_Octopus.jpg  
    Attached Files Attached Files

  • #5
    Join Date
    May 2006
    Posts
    7
    Hi Sinndho,

    Although your design is very interesting, I fail to see how to eliminate data redundancy or how to answer a query such as, "Which songs are in the top 100?" or "List all the songs by a specific artist."

    As this is my first attempt at designing a database with relationships and normalization, your approach seems to be well beyond my ability to understand it at this time.

    Don't get me wrong, I really appreciate the time and effort you put in to the DB design and explaining it, it just seems too complex for me to understand.

    I've found this DB on the internet that is close to what I'm looking for, but I still have one relationship issue. It's how the songs relate to the other tables.

    For instance:
    An Artist can have many Songs that are also on many Albums.
    Shania Twain - "Any Man Of Mine" is on both "The Woman in Me" & "Greatest Hits" albums.

    One Song can be performed by many Artists.
    "How Do I Live" performed by both LeAnn Rimes & Trisha Yearwood

    I could give several more examples, but I think that I've made my point.

    Am I working in the right direction or am I missing something here? Am I over-complicating this?

    Thanx for now,
    Cheers,
    ziggy1971
    Attached Files Attached Files

  • #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're in the right direction and you do not over-complicate but this is a rather complex matter.

    As I wrote previously the is a "Many to Many" relationship problem and, as far as I know, the only solution to it is the usage of an intermediate table, usually called a "junction table" (what I called "Associations") that creates the relationships among the different data tables. I probably over-did a little whith "the octopus" but the solution is in that way.

    You should try to google for "many to many" and see if you can't find some texts explaining the subject better than I'm able to do. Try for instance:
    SingingEels : Understanding SQL: Many to Many Relationships
    Beginner's SQL: Many-to-many relationships - SQL and Databases - DMXzone.COM
    Mitsu's blog : How to implement a many-to-many relationship using Linq to Sql ?
    Database Design - Many-to-many
    Understanding a SQL Junction Table Coding Notes
    Oracle - What is a junction table and how is it used in Microsoft Access?
    junction table ebook Download

    Have a nice day!

  • Posting Permissions

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