Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Music library MySQL database

I need some help building a table(s) for a music database in MySQL. I am unsure on how to lay this information out in a table.

Here is the info I would like to be stored 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.

Basically anything that has to do with organizing digital music. I'm new to databases and I have some ideas. But if I'm going to learn I might as well learn the right way of doing it.

Any thoughts on how to design my table(s) would be awesome.

like image 930
Throdne Avatar asked Feb 10 '12 20:02

Throdne


2 Answers

Something like this would be good to start with. It specifies a table for artists, albums (with keys into artists and genres), tracks (keyed into 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
like image 157
Michael Berkowski Avatar answered Oct 21 '22 10:10

Michael Berkowski


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 }
like image 27
Stelian Matei Avatar answered Oct 21 '22 10:10

Stelian Matei