Database Formatting for Album Tracks

Question

• I would like to store album's track names in a single field in a database.
• The number of tracks are arbitrary for each album.
• Each album is one record in the table.
• Each track must be linked to a specific URL which also should be stored in the database somewhere.

Is it possible to do this by storing them in a single field, or is a relational table for the track names/urls the only way to go?

Answer 1

Conventional approach would be to have one table with a row for each track (with any meta data). Have another table for each Album, and a third table that records the association for which tracks are on which album(s) and in which order.

Answer 2

Use two tables, one for albums, and one for tracks.

Album
-----
Id
Name
Artist
etc...



Track
-----
Id
AlbumId(Foreign Key to Album Table)
Name
URL

You could also augment this with a third table that joined the trackId and AlbumId fields (so don't have the AlbumId in the Track table). The advantage of this second approach would be that it would allow you to reuse a recording when it appeared on many albums (such as compilations).

Answer 3

Table: Album
ID/PK (your choice of primary key philosophy)
AlbumName

Table: Track
ID/PK (optional, could make AlbumFK, TrackNumber the primary key)
AlbumFK REFERENCES (Album.PK)
TrackNumber
TrackName
TrackURL

Answer 4

It's entirely possible, you could store the field as comma-separated or XML data for example.

Whether it's sensible is another question - if you ever want to query how many albums have more than 10 tracks for example you aren't going to be able to write an SQL query for that and you'll have to resort to pulling the data back into your application and dissecting it there which is not ideal.

Another option is to store the data in a separate "tracks" table (i.e. normalised), but also provide a view on those tables that gives the data as a single field in a denormalised manner. Then you get the benefit of properly structured data and the ability to query the data as a single field from the view.

Answer 5

The Wikipedia article on Database Normalization makes a reasonable effort to explain the purpose of normalization ... and the sorts of anomalies that the normalization rules are intended to prevent.