Thursday, March 27, 2014

Design a database to store music library for your jukebox

Problem
Design a database to store music library for your jukebox
Solution
Basically we have to save everything that has to do anything with organizing digital music
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. Also, all these tables will have a surrogate key(basically an auto generated primary key id, which will be a primary key, but not a real attribute of the table).


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

Note that override_artist gives us capability of choosing any other artist other than main album artist.

You can refer this diagram(courtesy):

References - stackoverflow

Reactions:

0 comments:

Post a Comment