On 7 Mar 2012, at 5:13pm, John Salerno <johnj...@gmail.com> wrote:
> On Mar 7, 7:33 am, Simon Slavin <slav...@bigfraud.org> wrote:
>
>> I don't understand the purpose of the table you originally described. If
>> you are listing the same tracks again and again, listing different times it
>> was played, then it would be useful to have three tables (artists, tracks,
>> plays). If each track is listed just once then I don't think you need a
>> separate table for tracks.
>>
>> Having three tables would make it very easy to list some statistics you
>> might not have originally planned on getting. You could, for instance, very
>> quickly list how many times each track had been played. Having people type
>> the full track name in every time would inevitably lead to them typing it
>> slightly differently sometimes, which would make full reporting
>> untrustworthy.
>
> Do you mean have a table that contains just the artist names, with an
> ID for each; then another table with song names, an ID for each song,
> and the ID for the artist; and then another table with play dates and
> times, with the ID of the song connecting it? I'm trying to figure out
> how it will all be connected.
Yes, this would be the 'normalised' form. Something like
artists: id, name, sortOrder
songs: id, artistID, title
plays: id, songID, playDate, playTime
When looking up things you would use JOIN a lot. For instance, to list all the
plays of a song
SELECT plays.playDate,plays.playTime FROM plays WHERE songID=345
To list everything you played on one day, in time order:
SELECT plays.playTime,artists.name,songs.title
FROM plays
JOIN songs ON songs.id=plays.songID
JOIN artists ON artists.name=songs.artistID
WHERE playDate = '20120308'
ORDER BY plays.playTime
Listing every song, and how many times it was played, in decreasing order of
number of times it was played, is harder. But see 'count(*)' in
http://www.sqlite.org/lang_aggfunc.html
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
