When to split a single database into two

[hpoincare]

I'm creating a database in Office2003 Access. This is my first time doing
this. After reading a manual, I'm still trying to understand what constitutes
a need for separate but linked databases?

My proposed database is to track inventory, log and monitor trouble tickets,
users, and document service and pertinent information of network devices. I
could have one very large database with a lot of tables (how many is "a lot"
or too many?).

or... should I break these up into 2 or 3 databases (and link them)?

Just looking for loose advice here - maybe some good book titles. Please
feel free to comment

 

[ruralguy via AccessMonster.com]

How about some links?

Split your Access database into data and application
http://allenbrowne.com/ser-01.html

Splitting your Microsoft Access MDB into a front end and back end
http://www.granite.ab.ca/access/splitapp/index.htm

 

[Arvin Meyer [MVP]]

Simple.

If your database is shared, split it. If it runs on a network, split it. If
you are continually working on the front-end, split it.

 

[Albert D. Kallal]

In the following article, I not only tell you to split, but expain the
general reasoning and philosophy who has to what you want to split EXPLAIN
WHY you want to split....

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

In the above I also explain that when you start developing a database, you
actually might not want to split the start of the project.

 

[Dennis]

First, a DATABASE is a COLLECTION OF TABLES. Each table contains related
information. Each table can have (IIRC) 255 columns (fields), and the
database can have (again IIRC) 255 tables. I have never seen a legitimate
reason to break a single database into multiple ones, and I've been in this
business a LONG time).

I HAVE seen (and recommend) that all database be SPLIT into a
front-end/back-end design, where the tables (in a stand-alone MDB file) are
linked to another MDB file which contains the forms, queries, reports,
macros, etc. There are many reasons for doing it that way.

Hope this helps. Oh, and take some database design classes. Really. learning
this stuff on your own will take many years, and you'll produce a huge volume
of crapware before you understand it.

 

[David W. Fenton]

I'm creating a database in Office2003 Access. This is my first
time doing this. After reading a manual, I'm still trying to
understand what constitutes a need for separate but linked
databases?

The fact that you've created an Access app constitutes sufficient
need to split.

More specifically:

A. Any shared access requires it.

B. Any case where application updates and data updates are going to
be separate needs to be split.

For instance, you might create an app for somebody who is the sole
user. Rule A would not require splitting, but if you need to update
the code while the user is maintaining data, then you need to split
the app.

Basically, I only have unsplit apps during the earliest prototyping
development stages, even when the apps are for a single user.

Splitting should be the norm and *not* splitting the exception.

 

[David W. Fenton]

Simple.

If your database is shared, split it. If it runs on a network,
split it. If you are continually working on the front-end, split
it.

That is a very simple statement, but I would say that we ought to
define this correctly, which is:

All Access apps should be split, except for these two exceptions:

1. in the earliest stages of application development (splitting
later)

2. when the app is used by a single user on a single workstation
without any code updates needed ever again once data has been
entered in the data tables.

Not splitting is THE EXCEPTION.