SQLite itself can do both single file, or multi-file handling through a single connection. Generally speaking, though, SQLite is a single file database. In order to use multiple files (AKA Multiple databases) in a single connection, you'd use the SQLite3 proprietary command of "ATTACH <DATABASEFILE>".
In your case of "Accounting, Dispatching, Service, Billing" etc, you can write individual tables within a single file to handle all your information. Then, your application could have a single connection to that database, and you'd build queries that would pull the required information together for your application to process. I don't know how dbase and foxpro handled information, and I've not read the book you've mentioned, but in that book you'll probably be introduced to the concept of normalization. What this means is that you break down a large chunk of data into components of data. For example, in a phone book, you wouldn't have one massive chunk of raw data for all of Canada. You've have one table to hold the provinces, one table to hold the counties/regions, another table to hold cities, and another to hold the different "types" of information (IE: Business, residential, public utility, etc), and another to hold the contact information. You'd also have one or more tables that would link all of this information together. Each table also gets its own internal index. The indexes can be of different types and what exactly is indexed, and how. Primary Keys would be an index to itself, a generalized "index" is also used, typically for foreign key to primary key relationships, unique constraints is also an index, and a couple of others as well. TL;DR--An example of your requirement types; - You'd have a set of tables that hold your customer contact information. Included would be company name, and contact information. - You'd have a single table that hold your invoices for billing, and one dedicated field that points to the ID of your contact customer table. - You'd have a single table that holds your billing items, and one one field that points to a row ID in the invoices table. - You'd have a single table that holds all your service items, or, products to be sold. - You'd have a single table that holds a list of your dispatch team members - You'd have a single table that holds information that points to the customer, your dispatch team member(s), the invoice and service they're dispatched out for. Of course, how the structure of the database is done is going to depend HIGHLY on how the organization works. But even with all of this, as far as your physical file system is concerned, you can have ALL of that stored within one file. Or... if you want... Multiple. Entirely up to you. On Thu, Aug 31, 2017 at 1:44 PM, John R. Sowden <jsow...@americansentry.net> wrote: > I have been using the xbase language (dbase, foxpro, etc.) for about 36 > years, writing applicatios for my alarm company, so each database is a > separate file for me. For the last 21 years, I have been using Linux, and > have found that sqlite is my best match for Linux database use. > > What I fail to understand is how I set up my files/databases. I have > categories that I write for: accounting, dispatching, service, billing, > etc. Some (most) of these use customer data, so when I am writing code for > the billing program, and I want to reference the customers, is that a > separate file, so I only have 1 customer file to update (the relational > model)? Having a customer table, with indices, in each category's > database (file) breaks the relational model. I have been on this list for > about a year and see no reference to this issue. I am reading now about > sqlite in _The Definitive Guide to SQLite_ by Michael Owens, but I'm early > in the book. > > Help? > > John > > _______________________________________________ > 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
