One thing to note is that there is an implicit "rowid" for each row already. Making an explicit ID column just adds an alias to it.
http://www.sqlite.org/lang_createtable.html#rowid Cheers, Larry On 9 March 2012 21:29, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
