On 8 Mar 2012, at 5:25pm, John Salerno <johnj...@gmail.com> wrote: > On Mar 8, 7:46 am, Simon Slavin <slav...@bigfraud.org> wrote: > >> Yes, this would be the 'normalised' form. Something like >> >> artists: id, name, sortOrder >> songs: id, artistID, title >> plays: id, songID, playDate, playTime > > Ok, between you and Larry's last post, it's starting to make a lot > more sense (except I don't intend to track "listeners"). I see now I > need separate tables for the artists and songs, but just a few > questions: > > 1. What is the sortOrder entry in the artists table you created?
Sooner or later you're going to want to make a list which is sorted in artist order. And you're going to want to list 'The Beatles' (if you have any taste at all). But if you list artists in name order they'd come in the Ts, not the Bs because 'The' is part of the band name. So having a "sortOrder" field lets you enter 'The Beatles' as the band name but 'Beatles, The' in the sortOrder field. Similarly, 'Elvis Presley' should be sorted under 'P', not 'E'. Of course you could enter 'Presley, Elvis' instead of the name in the order humans would say it, but I consider that a way of saying "The computer is more important that you so humans must adapt to the computer way.", which is an attitude I despise. > 2. Would there need to be an extra entries I should be aware of before > I begin, or is the table outline you created complete? I'm not quite sure what you wanted all this for to start with, but the above structure has enough in it to let you get started. You can add more tables and/or columns later. > 3. What is the purpose of the id entry for the plays table? Is it used > much, or is it just for completeness? It's more a habit than anything. Having an independent "id" column lets you refer to individual rows by something which should never, under any circumstances, need to change or refer to anything outside the database. For instance, Suppose you started off listing playTime as local current time of a play, and later you needed to convert all your times to a specific TimeZone, and remove any change for Summer Time. You would be changing existing data in the table in a way that meant any reference to that 'play' would have to be changed too. But by giving every table an 'id' column defined as INTEGER PRIMARY KEY (see http://sqlite.org/faq.html#q1 ) you know you will always have a consistent reference for any piece of data, even if one of the fields you care about needs to be changed. So people who work a lot with SQL databases tend to do this, or something like it, out of experience without even thinking about it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
