The "index" is on what's actually in the "table". So the index on each database's tables are always up to date.
What you're saying is that each satellite database should have a complete, up to date copy of the customer ID "table". The only way to do that would be to have a centrally accessible database with that info. Each of the satellite databases would connect to and ATTACH that central database to read the overarching customer table while they make local edits to their own local tables. The usual concepts of caching and such still apply, but yeah, you'd have 1 central db with everything that's needed by all satellite db's, and have those satellites connect to it as needed. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden Sent: Thursday, August 02, 2018 2:27 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Common index for multiple databases I made a mistake. I should have said table, not database. My concern is if I have 4 databases each with tables associated with a particular use, like accounting, technical, etc., which may reside on different computers, how do I keep the index in each database file current. I assume that I have an external database with the account number field, and its index that each database connects to to "refresh" its account number index from the external index. Otherwise if the table with the accounting index is modified, the tech table and its index would have to communicate with the master in order to stay current. I do this now because I have 1 account number index and the various foxpro databases (tables) all open that one index when each is used. John On 08/02/2018 10:31 AM, Simon Slavin wrote: > On 2 Aug 2018, at 6:11pm, John R. Sowden <jsow...@americansentry.net> wrote: > >> I do not want these databases to all reside in one sqlite file. How do I >> index each database on this customer account number when each database and >> associated index are in separate files? Is this what seems to be referred >> to as an external file? I assume that I would have to reindex each database >> each time it is opened, since a record could have been edited, etc. > You have been misinformed. In SQLite, > > A) each table is stored one database file > B) each index indexes just one table > C) all indexes for a table are stored in the same file as that table. > > An index is updated when its table is updated. You never need to manually > reindex unless you changed the table structure or index structure. > > It is normal to keep all tables related to one application in one big > database file. So, for example, if you run a library you would normally keep > tables and indexes for books, borrowers, and current loans all in one file. > And therefore all the indexes for those tables would be in that file too. > SQLite is designed to handle things this way, and does it very efficiently. > > However, it is possible to keep different tables in different database files. > So you might keep books (and all indexes on books) in one file, and > borrowers and current loans (and all the indexes on those tables) in another > file. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
