SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    Rusted & Weathered HumanClay's Avatar
    Join Date
    Sep 2000
    Location
    Milwaukee, WI
    Posts
    225
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I am in the process of making a music discography database with MySQL, and I have a question on how to set it up.

    Right now, my idea was to create two different tables, one for the discs themselves, containing information about that particular CD, and then another containing all of the songs released by the group with lyrics, notes, etc.

    My question is what would be the best way to add what songs are on the specific disc? Since discs have differnt number of songs, it would be very inefficient to just make 15 or 20 different fields that would contain song titles. So what is the best way to do this?

    Thanks,

    Steve
    <Edited by HumanClay on 01-09-2001 at 08:35 PM>

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That plan is loaded with problems...

    First you only want 1 DB, what you'll need is multiple tables.

    Then you don't want to put song names in fields.

    You'd need 2 tables.

    Code:
    Table 1 Albums
    
    Artist
    Album
    Date_Published
    Copies_Sold
    Album_ID
    etc
    Code:
    Table 2 Tracks
    
    Album_ID
    Song_Name
    Song_ID
    etc
    So each song will have a row in the Track Table. Your DB output may look like this

    Code:
    23 Jeremy    256
    23 Daughter  257
    Etc etc etc

    So when you want to print out all the songs from 1 album you'd do:

    Code:
    Select Song_Name
    From Tracks
    Where Album_ID  = $ID
    Any more questions, feel free to ask.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    Rusted & Weathered HumanClay's Avatar
    Join Date
    Sep 2000
    Location
    Milwaukee, WI
    Posts
    225
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I understand that. Now, lets say the songs are on more than one disc. For instance, "Jeremy" is on two different discs, "Ten" and "Jeremy Promo" (don't know if thats a real disc but you know what I mean). I'd rather not list the songs more than once because the songs that I will be using are on several discs, and songs would be listed over five times then.

    So, how would I list songs once in the table, but have them point to multiple DISC ID's?
    Steve Caponetto - [profile] [e-mail]
    CreedFeed.com - feed your need for Creed!

  4. #4
    SitePoint Evangelist mad-onion's Avatar
    Join Date
    Aug 2000
    Location
    Land of the long white cloud
    Posts
    556
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with what aspen said except that i would creat another table called artists.
    Also you might want to provide artist profiles and it would be easier to have each artist only entered once.

    So the albums table would be like
    Artist_ID
    Album
    Date_Published
    Copies_Sold
    Album_ID
    etc

    And then you would have another table called artists
    ID
    Name
    Type - band/solo
    etc

    How you set things out really depends what features you are intending to have.

    GoodLuck!
    SiteOptions >> Services :: Products :: Contact
    Developers of PHP, C++, Visual Basic, MySQL, and more!

  5. #5
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would list them more then once in the DB. But that doesn't mean you would have to display multiple copies of the same song at one time.

    Code:
      Song_Id | Song_Name | Album_ID | Artist_ID
        243       Jeremy      23         16   
        243       Jeremy      24         16
    So the song would be listed twice, but would have the same song ID, only different album_ID

    This way Jeremy is listed as on both Album 23 and Album 24. But if you just want to list all Pearl Jam songs you only want to list each song once right? So you just add the DISTINCT qualifier to your SELECT statement.

    SELECT DISTINCT Song_Name
    From Tracks
    Where Artist_Id = $AID

    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  6. #6
    Rusted & Weathered HumanClay's Avatar
    Join Date
    Sep 2000
    Location
    Milwaukee, WI
    Posts
    225
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aspen,

    Thanks. That is what I was looking for.

    One other question, not really relating to this topic, but is there any way to easily convert an Access database to a MySQL database? I started working on the database in Access just to see visually how it would look because my webhosting account isn't set up yet. Do I have to manually type everything into MySQL or is there some sort of converter?

    Thanks again aspen,

    Steve

  7. #7
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I jump in? One thought, if you add a 'Type' to the artists table, it should be a foriegn key from a 'Types' table.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  8. #8
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is a couple of ways to do this. You could export the Access DB as a flat text file and then import into MySQL using something like MyPHPAdmin

    But I do believe MySQL has programs that does this for you. Do a search in these forums because I think this was answered yesterday even, it was recently. Or browse through the MySQL website.

    <edit>
    Yup... yesterday

    http://www.sitepointforums.com/showt...threadid=14548
    </edit>
    <Edited by aspen on 01-09-2001 at 11:14 PM>
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  9. #9
    Rusted & Weathered HumanClay's Avatar
    Join Date
    Sep 2000
    Location
    Milwaukee, WI
    Posts
    225
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks

  10. #10
    SitePoint Member
    Join Date
    Jan 2001
    Location
    Portland, OR
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a music discography site at http://www.discogs.com. You might want to check that out.

    I don't want to give away all my secrets but I think it's better to spread data across many tables than try to cram it all into one. For example, I think you should definitely keep individual tracks in one table and discs in another.
    Discogs - Building the definitive electronic music database...

  11. #11
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You really should have a song_album table:

    song_id, album_id

    song_id would reference the song in the songs table and album_id would reference the album it belongs on, it is more efficient then doing the following:

    Song_Id | Song_Name | Album_ID | Artist_ID
    243 Jeremy 23 16
    243 Jeremy 24 16

    As in the above you have song_name and Artist_ID duplicated which you really don't want.

    If you want anymore info or are struggling I can probably do you a database design that you can implement.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  12. #12
    SitePoint Evangelist mad-onion's Avatar
    Join Date
    Aug 2000
    Location
    Land of the long white cloud
    Posts
    556
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is slightly off topic but hey!..

    I know it is possible to store binary data in MySQL tables (done it with an image library once) so would it be possible to store music.
    It would probably not be practical but im wondering if it would be possible...
    Any opinions?

    SiteOptions >> Services :: Products :: Contact
    Developers of PHP, C++, Visual Basic, MySQL, and more!

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    mad-onion,

    It's definitely feasible. Nothing says you can't store MP3 files in a MySQL database, for example. The only thing I'd worry about is that if your music library grows really big you might butt up against the file size limitations of your operating system (since the data for each table in MySQL is stored in a single file). A more scalable solution might store the files in a directory and reference them by filename in the database. Less sexy, I know, but probably more sensible.

    As for the database design discussed at the top of this thread, Steve (HumanClay) had the right idea when he said he'd rather not list a single song in the database more than once if it appears on multiple albums. By creating a third, 'lookup' table to associate tracks with albums, you could avoid this sort of redundant storage.

    For full details and an example of lookup table use in database design, see Chapter 5 of my tutorial, Building a Database-Driven Web Site using PHP and MySQL.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  14. #14
    SitePoint Evangelist mad-onion's Avatar
    Join Date
    Aug 2000
    Location
    Land of the long white cloud
    Posts
    556
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi kevin, you been away for a while! Great to see your wisdom returning.
    I wasnt actually intending to store MP3's in a db .. Its just one of those things! (like you say) Its sexy to nerds, the thought of storing music in a db.


    SiteOptions >> Services :: Products :: Contact
    Developers of PHP, C++, Visual Basic, MySQL, and more!

  15. #15
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with Karl's comments - what he says is in tune with data-normalization. You *really* need to read up on data normalization and data modelling. I don't know of any good web based resources off the top of my head. But it is really important to model your database correctly up-front. It is the most dificult thing to go modifying later on - real messy to muck aboutr with your database schema once its up. Especially if you've already programmed all your front-end (web pages) using the old schema then you've got double the mess!

    You are already on the right track HumanClay as what you've been observing in your data are "one-to-many" relationships (ie, the same song title may be on one or more albums). Whenever you come up with that type of relationship you need to create a bridging entity (table).

    for example:

    Table Songs:

    song_id,
    song_title,
    song_length, etc ...

    Table Albums:

    album_id, (PRIMARY KEY)
    album_name,
    album_artist_id, etc ...

    Table Artist:

    artist_id, (PRIMARY KEY)
    artist_LastName,
    artist_FirstName,
    artist_dob, etc ...

    Now to be able to relate these entities (tables) to each other you create "bridging" (there's a technical term but its slipped my mind) entities (tables). eg;

    Table Song_Album

    song_id,
    album_id,
    song_album_id (PRIMARY KEY)

    so for each song you have a seperate row in the database for each time that song appears on an album.

    eg:
    lets say you have a song with song_id = 123 and it has been on three recordings with album_id of 111, 222 and 333. then the entries in Song_Album would be:

    123, 111
    123, 222
    123, 333

    Now you have a way of being able to relate a song to the albums that contain it. You can write an sql statement

    SELECT * FROM Album, Album_Song WHERE
    song_id = '123'

    and the result set will be all the details from your Album table for those albums that contain that song title.

    It is important to note that the only fields in the "bridging" (dang what is the correct term!) tables are the primary keys from the associated tables (which are refered to as foreign keys because they reference to the record in the parent tables that holds all the meaty data) and the primary key. In the table song_album above, "song_id" and "album_id" are both foreign keys as they reference the actual records in the tables "song" and "album" respectivley. All the meaty data about a song or an album are fields in the Song or Albums tables. This is to eliminate data redundancy.

    Data what?...

    well lets say that in your database you want to record the first line of every song so that if someone knows the first line but not the name they can do a search on it at your web site. So, you have a field "first_line". Putting that field in the Song_Album table is not a good idea. Why, because you have to unnecessarily repeat that data across several records. Eg:

    lets say pink floyd's "Wish you were here" has an album_id of 111 and the title song "Wish you were here" has the song_id of 123. Then if we put the "firstline" field in the Song_Album table we would end up with an entry like this...

    song_id album_id firstline
    123 111 So, so you think you can tell

    Ok what's the problem with that. Well lets say your collegue is updating the database and wants to add a record because that song is also on the "Pink Flody - Live in Rome '75" album (album_id = 222). Problem is your collegue is a bit hard of hearing so he enters

    123 222 So, so you think I smell

    Now you got corrupt data!

    So its better to include the firstline field in the Song table. This way it only ever has to be entered once when you add a new Song to that table.

    Anyway, I'm probably rambling on now. It would be better if some others could post some links to more authorative references. I think Kevin Yank talked about data modelling in his series of articles on PHP and databases at http://www.sitepoint.com

    cheers and best of luck
    frealysid

  16. #16
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    596
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically,


    when you have a many-to-many relationship you don't want to connect your tables like that. you would want to have a table in between those two tables that would carry "repeat records". for instance:
    Code:
    table 1
    cd_code  etc....
    =================
    cd_name1
    cd_name2
    cd_name3
    
    
    table 2
    cd_code  song_code
    ===================
    cd_name1   song_code1
    cd_name2   song_code2
    cd_name2   song_code1
    
    
    table 3
    song_code  etc.....
    ===================
    song_code1  .....
    song_code2
    song_code3
    :
    :

    do i make any sense? A book that a DBA recommended to me was Oracle Database Design by O'Reilly...it's awesome and very easy to understand!


    Regards,

    Sam

  17. #17
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL DLG_1 - that's what I'm saying! Sorry if my rambling is confusing.

  18. #18
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    All of these issues are covered in Part 5 of my "Building a Database-Driven Website" series, called "Relational Database Design". If you don't have a good book on database design, you should at the very least take the time to read and understand that section of my series. In many ways, I consider it the most important section of the article.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


Bookmarks

Posting Permissions

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