Loosely speaking, I put things together in the same file if some of those things are necessary to understand or interpret the other things, or if there is a logical dependency between things (say, a foreign key), they go in the same file. Being in one file ensures that all the interdependent parts are kept together. If A and B can be fully understood in complete isolation from each other and have no mutual constraints, it is safe to have them in separate files. If SQLite supported user-defined data types, the type definitions and tables etc based on them would need to go in the same file. Triggers and views and the tables they work on need to be in the same file. -- Darren Duncan
Eric S. Johansson wrote: > what's the general rule for deciding when to put multiple tables within a > single > sqlite db file? I think the answer is something like you put tables together > in > one database file if they refer to different aspects of the same data element > and you put them in separate database files if there's no connection except > the > databases are used in the same application. For example, in my case, I have > one > table which contains the raw original data, a thoroughly cooked form of the > original data, and a series of data elements which are used for searching and > display. The related table contains information derived from postprocessing > and > will also be used for searching and graphing. The second table's information > could be regenerated anytime at a cost of running through every record in the > database and recalculating it. As a result of this association, I figure it's > appropriate to place both records in the same database file. > > The third table tracks data from another part of the process and has no > connection to the first two tables except that it is used as part of the > postprocessing calculations that feed the second table described above. I > figure the third table should go in its own database. > > For what it's worth, record counts could easily cross 100,000 for each one of > the tables. Hope it's not time for mysql :-) > > Thanks for a feedback. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
