Re: [sqlite] Question of Table/Indices common to multiple Databases

[John R. Sowden]

Thank you all for your feedback.  I now have a lot more to digest.  I 
will investigate the attach command.  I am concerned about keeping all 
of my company's data in 1 file, as if something happened to that file, I 
would have data entry, programming, etc. to on all systems since the 
last backup, not just 1.  That might cause a day's worth of  'business 
interruption'.


On 08/31/2017 07:15 PM, Keith Medcalf wrote:

Now, there are some unusual situations where you might want to hold
your customers in a different database from your invoices and use
SQLite to hold both databases open at once using ATTACH.  I’ll let
other people argue about that.

But of course if you do that, then you cannot have the database enforce 
referential integrity and you have to do it yourself.




___
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

Re: [sqlite] Question of Table/Indices common to multiple Databases

[Keith Medcalf]

>Now, there are some unusual situations where you might want to hold
>your customers in a different database from your invoices and use
>SQLite to hold both databases open at once using ATTACH.  I’ll let
>other people argue about that.

But of course if you do that, then you cannot have the database enforce 
referential integrity and you have to do it yourself.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question of Table/Indices common to multiple Databases

[Keith Medcalf]

Why do you want multiple databases?

All the "Data" goes in one "base".  Hence the term "database".


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden
>Sent: Thursday, 31 August, 2017 11:45
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Question of Table/Indices common to multiple
>Databases
>
>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

Re: [sqlite] Question of Table/Indices common to multiple Databases

[Stephen Chrzanowski]

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
".

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 
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

Re: [sqlite] Question of Table/Indices common to multiple Databases

[Simon Slavin]

On 31 Aug 2017, at 6:44pm, John R. Sowden  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)?

I’m going to let other people answer your question, but I wanted to give you 
some words to use in thinking about it because SQL doesn’t work the same way as 
xbase.

When using SQLite, a database is a file on disk.  Generally speaking each 
SQLite application has one database open at one time.  Behind the scenes SQLite 
creates temporary files, journal files, etc. depending on what you’re doing, 
but as far as your code is concerned you’re only worried about one file per 
database.

A collection of similar things is a table.  So you will have a table of 
customers, a table of orders, a table of despatches, etc..

Each database can hold many tables.  It’s normal to have all the tables one 
application would need at one time in a single database.  So your program would 
open one database (one file on disk) and in that would be all your customers, 
orders, customer service tickets, etc..  an application can ignore any tables 
it doesn’t care about.  So your salespeople don’t need to see your customer 
service tickets, and your customer care people don’t have to see your 
dispatches.

Now, there are some unusual situations where you might want to hold your 
customers in a different database from your invoices and use SQLite to hold 
both databases open at once using ATTACH.  I’ll let other people argue about 
that.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question of Table/Indices common to multiple Databases

[Gwendal Roué]

Hello John,

ATTACH DATABASE may well be the statement that you need: 
https://www.sqlite.org/lang_attach.html 


It lets you use several sqlite files from a single database connection, and 
execute queries across all tables of all attached files.

Gwendal Roué

> Le 31 août 2017 à 19:44, John R. Sowden  a écrit :
> 
> 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

[sqlite] Question of Table/Indices common to multiple Databases

[John R. Sowden]

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