SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2001
    Location
    usa
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm creating a database of albums for a project at school and need some assistance in designing it.
    What I would like to do, is have a table called "albums" which will have fields for artist, album title, songs, genre and run time.
    I'm a bit stumped as far as finding the best way to do this. This table will have a fixed number of fields, so how will I enter albums that have different numbers of songs?
    Maybe I should create a table for each albums song list? That way It wouldn't matter how many songs were on each album, because I could create each table dynamically after entering each albums songs in a form? And then link each albums song list table to the main table with a unique ID?
    Any thoughts? Is there a better way?

  2. #2
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    suggestion -
    Code:
    tblAlbums
    -albumID (Primary Key, unique)
    -albumTitle
    -albumDescription
    -albumRunTime
    -artistID (Foriegn Key from tblArtists)
    -genreID (Foriegn Key from tblGenres)
    
    tblArtists
    -artistID (Primary Key, unique)
    -artistName
    
    tblGenres
    -genreID (Primary Key, unique)
    -genreName
    -genreDescription
    
    tblSongs
    -songID (Primary Key, unique)
    -songName
    -songRunTime
    -artsistID (Foriegn Key from tblArtists)
    
    tblAlbumSongs (this is a join table needed for many-to-many relationships)
    -albumID (Primary Key from tblAlbums)
    -songID (Primary Key from tblSongs)
    A couple things to keep in mind. 1) Even though there's a lot more then what you had specified, it's really a bare minimum in order to have the function you want without the data anomalies that would otherwise occur. 2) Data entry concerns come after the design. Views and data entry are based on queries that could combine tables.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  3. #3
    SitePoint Member
    Join Date
    Feb 2001
    Location
    usa
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help, Westmich. That looks good, except I don't follow you on the function of tblAlbumSongs. Can you give an example on how it would be used?

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    This is called a lookup table. You need one of these whenever you want to represent a many-to-many relationship in a database.

    Try reading Chapter 5 of my article, Building a Database-Driven Web Site using PHP and MySQL. It introduces all of these database design issues with concrete examples. After you've read that, the suggested database format above should make a lot of sense.

    If not, feel free to post back and we'll try to explain further.
    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

  5. #5
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with Kyank.

    I can put down a decent db structure on a forum response, but it would be quite another thing to explain all the principles behind it

    Kyank tutorial would be a good place to start.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com


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
  •