On Thursday, September 23, 2010 3:36 PM, Simon Slavin wrote: > On 23 Sep 2010, at 2:13pm, Andy Gibbs wrote: > >> I've got a table with a primary key and then any number of additional >> tables >> with foreign keys that reference this primary key table with "ON DELETE >> RESTRICT" > > I always worry when I see descriptions like this. Those additional > tables: do they all have the same columns ? If so, can you amagamate > them all into one big table ? Just insert one extra column saying what > kind of row this row is. > > Not only does this fix the problem you raised, but it means you don't > need to change your schema each time you encounter a new type of > information.
Thanks for the suggestion, Simon. If only it were that simple. Unfortunately, each of the foreign key tables are actually quite distinct in their purpose, so putting them all into one huge table would not be the right solution. The primary key is a timestamp (as an integer, i.e. number of seconds since some arbitrary epoch or other). The primary key table holds then the "common" information on the "action" that has happened, i.e. timestamp, user name, and some other data. The foreign key tables are all those that hold data for the particular actions that can be done, but really they are very very different from each other. Of course it would have been possible instead to merge the columns from the primary key table into each of the foreign key tables and not have the primary key table, but the really nice thing about keeping the common data it central, is that only one table needs to be queried e.g. to find out the which users have been making alterations to the system and when (this is one of the main design requirements). It seems to be a trade-off -- either the complexity is in the DELETE statement to keep the primary key table tidy or in the SELECT statement querying it. If it has to be a choice, then the complexity has to be in the DELETE statement since this happens very infrequently. Cheers Andy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
