Question of Table/Indices common to multiple Databases

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question of Table/Indices common to multiple Databases

Gwendal Roué-2
Hello John,

ATTACH DATABASE may well be the statement that you need: https://www.sqlite.org/lang_attach.html <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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question of Table/Indices common to multiple Databases

Simon Slavin-3
In reply to this post by John R. Sowden
On 31 Aug 2017, at 6:44pm, John R. Sowden <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question of Table/Indices common to multiple Databases

Stephen Chrzanowski
In reply to this post by John R. Sowden
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question of Table/Indices common to multiple Databases

Keith Medcalf
In reply to this post by John R. Sowden

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-
>[hidden email]] On Behalf Of John R. Sowden
>Sent: Thursday, 31 August, 2017 11:45
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question of Table/Indices common to multiple Databases

Keith Medcalf
In reply to this post by Simon Slavin-3

>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users