MySQL Music Database

Question

I need help creating a table for a music database in MySQL. I am not sure how to put this information in the table.

Here is the information that I would like to store in the database.

Artist Name Album Name Release Date Genre Picture URL (album artwork) Track Number Trank Name Track Playtime Lyric (optional, But would like to have it someday) etc. 

In principle, everything related to the organization of digital music. I am new to databases and I have some ideas. But if I am going to study, I could also learn the right way to do this.

Any thoughts on how to create my tables would be great.

Answer 1

Something like this would be good to start. It defines a table for artists, albums (with keys in genres of artists and genres), tracks (embedded in albums) and genres.

 Table artists ---- id (primary key), name description years_active otherinfo (whatever you need) Table albums ---- id (primary key) artistid (foreign key to artists table) name, releasedate genreid (foreign key to genres table) picture Table tracks ---- id (primary key) albumid (foreign key to albums table) name override_artist (overrides album artist if not null) playtime lyric otherstuff as needed Table genres ---- id (primary key) name description 

Answer 2

I suggest the following database structure:

 artist { id, name } genre { id, name } album { id, name, artist_id, release_date, genre_id, picture_url } track { id, album_id, number, name, playtime, lyrics } 

Answer 3

 Artist ( ArtistID INT PRIMARY KEY, ArtistName ) Genre ( GenreID TINYINT PRIMARY KEY, GenreDescription ) Album ( AlbumID INT PRIMARY KEY, ArtistID INT, GenreID INT ReleaseDate ) AlbumArt ( AlbumArtID INT PRIMARY KEY, AlbumID INT, AlbumArtPath ) Track ( AlbumID INT, TrackNumber INT, TrackName, PlayTime, Lyrics , PRIMARY KEY ( AlbumID, TrackNumber ) ) 

Answer 4

A database schema is the planning of tables and the establishment of relationships between these tables.

You have already identified many candidates for the table: Track, Artist, Album

Then you need a relationship: an artist can have many albums, an album can have many tracks.

With these few simple tables and relationships, you already have a small database.

Answer 5

Check out http://www.freedb.org/en/ . Formerly cddb.org, it is a free music database. You can download the entire database from one of your links and see how they did it.