It sounds like you want a log of timestamps for the time that each song is played. You also want to store who listened to the song and other information about the song such as the artist.
Since the only variant is the timestamp, if you want to normalise the data you can isolate all the other textual items to separate tables and reference those by ID. For example: Table: Listener ID=1 Name=Larry Table: Song ID=1 Title="Hippy Hippy Shake" Artist="The Beatles" Table: Log Listener=1 Song=1 Timestamp=8 March 2012 20:59 So your Log table can grow and for each new record you only store two integers - the references to the Listener ID and the Song ID. The timestamp is the only bit you store in full each time there. If a new Listener comes along, you give them an ID and then you can create Log entries for the new Listener. If a new Song comes along, you give it an ID and ... you get it. You are free to extend the Listener table and Song table without impacting the Log table (so you can extend the functionality of your database), e.g. if you decide you want to know the Year that the Song was released. You only need to enter it once in the Song table. You could even break-out the Artist to a separate table, e.g. Table: Artist ID=1 Name=The Beatles Then the Song table becomes: Table: Song ID=1 Title="Hippy Hippy Shake" Artist=1 So to put it all together again, you need to use a SQL JOIN statement, e.g. SELECT Listener.Name, Song.Title, Artist.Name, Log.Timestamp FROM Log JOIN Listener on Listener.ID = Log.Listener -- this maps the Listener table so you can SELECT the Listener.Name above JOIN Song on Song.ID = Log.Song -- this maps the Song table so you can SELECT the Song.Title above JOIN Artist on Artist.ID = Song.Artist -- this maps the Artist table (using the Song mapping) so you can SELECT the Artist.Name above WHERE Listener.Name = "Larry" The point with normalisation is to remove the invariant data to another table, assign it an ID and then reference it by ID instead of duplicating the invariant text. It also allows you to "encapsulate" your data (think OOP) so for example, Song info is in the Song table (class) and you use an ID (pointer) to each row (instance) of Song. Hope that clears things up. Cheers, Larry On 7 March 2012 04:48, John Salerno <johnj...@gmail.com> wrote: > Thanks to both of you. The question of normalization was one thing I was > considering, I just wasn't sure how it should be done. A separate table > with Artist ID and Artist Name fields could be useful. Would it be good to > make an equivalent table for the songs, or should the songs simply be > listed individually in the main table? I assume the dates and times have to > be individual entries each time. > > However, while reading the Wikipedia page on normalization, I noticed that > some of the examples of a normalized table actually lists people's names as > separate entries in the table. Of course, the alternative was some kind of > nested tables, that looked messy, so perhaps the separate listings were the > first step toward normalization, the next step being multiple tables with > IDs for the names? > > Thanks. >> >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
