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
