On Wed, 13 Oct 2010 18:15:54 +0300 Mihai Militaru <mihai.milit...@gmx.com> wrote:
> But I got this idea: what if you create such "forms" yourself, using the > flexibility given by SQL? I don't know whether it would satisfy all your > requirements, but at least for duplication of data you can easily use > temporary "tool" tables with triggers, eg. you create a table using the > required fields (both named purposefully), and then triggers attached to it > can update different things on different target tables. 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 >> allow a new record to be added when it involves more than one table. To make a view facilitate updating of the underlying tables, you need to add "instead of" triggers, which tell the database exactly what do do to tables instead of modifying the view. I think this is a superior approach to handling all the data redirection in your application code or by creating temporary tables etc. Using views and triggers makes the solution portable between applications and consistent within the schema itself. I'll see if I can put together some SQL with a few examples of how a view updates several related tables. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
