Thanks for the suggestion, Tom, your post was very educational. At least in my case, you're on the spot, I knew about views but I didn't use them in my project so far, although a short analysis tells me that they would be extremely useful for it - for example I should create one called "files" for tables "filenames" (unique path names) and "fcontents" (unique checksum) in a left outer join by the content index (int64). Guess what I'm doing next :).
Anyway, I see the great advantage and also how easily forms can be created with such views. Regards, Mihai On Fri, 15 Oct 2010 17:45:53 +1100 BareFeetWare <list....@tandb.com.au> wrote: > You don't need to create special tables for data entry. You can > create views instead, coupled with "instead of" triggers. > > This is one of the most undersold features of SQL and SQLite in > general, I think. > > When you design a database, you should properly structure and > normalize it so that, for instance, there is no redundancy in data > entry, a column that has an occasional entry is moved to its own > table (rather than populating the main table with nulls), a multiple > choice column contains an integer that links to a related table of > values and so on. Dates should probably be entered as a julian day > real (rather than text) and money should probably be entered as an > integer (in cents) rather than a real, to avoid float calculation > errors. > > All that works well from a data integrity point of view. It's tight, > uses minimal space and avoids redundancy and inaccuracies. But if you > give such a properly normalized database in its raw form to a use for > data entry (or even to browse data), it's horrible. They could go > crazy trying to cross reference tables, mentally convert dates and > money, switch to a separate table for occasional column info etc. > > The answer is to create a view. This is pretty common place. In a > view, you can join tables, show just some rows, show calculated > totals, convert date reals to human readable text, integer amounts to > currency etc. > > But what few database designers seem to exploit is the fact that > views can also be used to accept user input and convert that into > meaningful entry into the underlying related tables. And, to answer > the original poster's query, a view can -- Mihai Militaru <mihai.milit...@gmx.com> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
