On 6 Mar 2012, at 5:30am, John Salerno <johnj...@gmail.com> wrote:
> The point is that I want to create a database storing these four pieces of
> information and then later be able to retrieve the information in multiple
> ways, such as selecting a date and song and seeing how many times it was
> played that day, or selecting a date and person, or just a song, or just a
> person, etc.
All that is standard SQL. Just get the data in and SQL will look things up,
using an index if a good one is available or scanning the records if one isn't.
However, remember that if you are going to want to search and sort by date
order it's best to store dates in an order which is naturally sortable. So
store a Julian Date number, or perhaps in the form YYYYMMDD (e.g. '20120306'
for today). Don't store them in an order that's designed for humans to read.
> So I'm wondering what the best way to create such a database would be.
> Should each of those four pieces of information be an entry in the table ?
The only question I see is whether you store the artist's name as just another
text column in your table, or give every artist an ID number and store those
instead. You'd then make another table which let you look up artist's names
and get their ID number, or vice versa. There are pros and cons to each
approach, all dealt with by any beginner's book on SQL.
> [snip]
>
> c.execute('create table songs (date text, time text, artist text, title
> text)')
You may right now know what that date means, but date is the /type/ of the
data, not what the data represent. What date and time is it ? The date that
the recording was made ? The date you acquired the recording ? The date you
last played the song ? I recommend you call those fields "AcquiryDate" or
"LastPlayedDate" instead.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
