This is basically where I am coming from, but deletion (of possibly millions of entries) is slow and ties up the system (see previous thread by someone some days back). In experimentation having dynamic tables and doing a drop table is proving a big winner, though so far only using about a dozen tables.
On 16/10/2010 10:32 AM, Scott Hess wrote: > Having a table with an owner_id, key, and value, with a unique index > on (owner_id, key) will probably be more efficient than having a > separate table per owner. Also, it will be easier to code safely, > because bind parameters don't work on table names (I'm assuming you're > using dynamic table names in this case - if so, watch out for johny > drop tables). > > -scott > > > On Fri, Oct 15, 2010 at 4:26 PM, Andrew Davison > <andrew.davi...@gmail.com> wrote: >> There are no schemas per se. Just key+blob. They are backup datasets. >> Nothing fancy databasey. Just wondering about the actual impact of >> having many tables. >> >> On 15/10/2010 6:54 PM, Simon Slavin wrote: >>> >>> On 15 Oct 2010, at 7:36am, Andrew Davison wrote: >>> >>>> What's the take on having hundreds of tables in a database? >>> >>> Generally not. A database should be designed. By a human. I don't know >>> about you, but I can't hold hundreds of schema in my head at the same time. >>> Rather than have two or more tables with the same schema, it's usually >>> better to have one table with an extra column to mark what kind of data >>> each record is. There are exceptions to this but it's a good design >>> principle. >>> >>>> Any likely >>>> performance problems apart from first time a table is accessed? Does it >>>> affect the cache? >>> >>> SQLite keeps data from each table (and each index) in different pages of >>> filespace. So each time you switch from one table to another you're >>> switching to another page of the file. And if you have 100 tables in a >>> file you have 200 pages of space, reserved for only one kind of data that >>> can't be used for anything else. That's an argument for fewer but bigger >>> tables. >>> >>> I understand why you asked the question but I think that an SQLite newbie >>> can only figure it out from experience. My advice is to design stuff >>> whatever way makes it simplest for you to do your programming. Worry about >>> performance only if it turns out to be too slow or too unwieldy or annoying >>> in some other way. >>> >>> 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 >> > _______________________________________________ > 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
