When to make separate databases?

**salad** · Jul 13 '06, 01:25 PM

Re: When to make separate databases?

LurfysMa wrote:

I would like to hear opinions on the tradeoffs of putting the tables,
forms, and queries for several related datasets in separate databases
vs one combined database.
>
I am working on an application that will have a number of "subjects".
The subjects have a number of commonalities, but are not identical. I
am hoping that I will discover that the subjects fall into a few
"types" and a common database structure can be used for each type.
>
Each subject will have a few (1-5?) tables, queries, forms, etc. They
have a lot of similarities, but are not identical. Some subjects will
have more tables than others. Even the tables that are the same may
have some different fields.
>
Initially, I had all of the subjects in one database. I actually tried
to use the same tables for all of the subjects. This quickly became
unweildy.
>
Now I want to make separate tables, forms, & queries for each subject.
If I find that there are commonalities, I will combine them later.
>
The questions is whether to put each subject in a different database
or just make separate tables, forms, & queries in a combined database.
>
I am inclined to make separate databases. My reasons are as follows. I
would appreciate comments on whether my reasons are valid or if there
are other considerations.
>
The advantages of separate databases as I see it are:
>
1. Smaller database files, easier to transport?

Is that really a consideration? Zipping an MDB should shrink it quite a
bit. How often do you "transport" tables?

>
2. Slightly easier to replace an entire db than a table within a db.

Unsure what you mean here.

>
3. Simpler naming. No need to identify the subject.

And worse for debugging.

>
4. Better for incremental backup. Changes to one subject would not
affect a huge database file.

What is huge?

>
The disadvantages are:
>
1. More files to manage.

Irrelevant

>
2. Where to put the master subject table?

In a primary backend with most other shared tables.

>
3. Somewhat more complex for the application.

KISS.

I am not sure if there is any difference performance wise.
>
Comments?
>

Just how many MDBs are your going to end up with? Are you subjects
static or do you add subjects on a daily basis?

Assuming your subject count is low, I'd go with 1 mdb. I would call the
Subject table Subject. And all others something like SubjectBirds,
SubjectReptiles , SubjectFish...e tc.

I might see if there is a commonality to some fields. Put the subjectID
(to link to the Subject table), datefields, flag fields, subjecttype in
a common table. Then I'd create tables that would link to this common
subject file for your various subjects. Then I would create queries
that would link the subject, master subject, and the table for the
non-common fields and name them SubjectBirds, SubjectReptiles ,
SubjectFish...e tc.

I would think that you'll need to create separate forms and reports for
each subject type.

Sounds like you have a system with lots of overhead and frustration in
the future. Keeping it in one backend MDB, as far as I'm concerned,
will be less hassle.

**Joseph Meehan** · Jul 13 '06, 01:35 PM

Re: When to make separate databases?

LurfysMa wrote:

I would like to hear opinions on the tradeoffs of putting the tables,
forms, and queries for several related datasets in separate databases
vs one combined database.
>
I am working on an application that will have a number of "subjects".
The subjects have a number of commonalities, but are not identical. I
am hoping that I will discover that the subjects fall into a few
"types" and a common database structure can be used for each type.
>
Each subject will have a few (1-5?) tables, queries, forms, etc. They
have a lot of similarities, but are not identical. Some subjects will
have more tables than others. Even the tables that are the same may
have some different fields.
>
Initially, I had all of the subjects in one database. I actually tried
to use the same tables for all of the subjects. This quickly became
unweildy.
>
Now I want to make separate tables, forms, & queries for each subject.
If I find that there are commonalities, I will combine them later.
>
The questions is whether to put each subject in a different database
or just make separate tables, forms, & queries in a combined database.
>
I am inclined to make separate databases. My reasons are as follows. I
would appreciate comments on whether my reasons are valid or if there
are other considerations.
>
The advantages of separate databases as I see it are:
>
1. Smaller database files, easier to transport?
>
2. Slightly easier to replace an entire db than a table within a db.
>
3. Simpler naming. No need to identify the subject.
>
4. Better for incremental backup. Changes to one subject would not
affect a huge database file.
>
The disadvantages are:
>
1. More files to manage.
>
2. Where to put the master subject table?
>
3. Somewhat more complex for the application.
>
I am not sure if there is any difference performance wise.
>
Comments?

I can't make any specific recommendations from the information you have
presented since I really don't understand your situation.

First will there be more than one person accessing the data at the same
time? This is usually the reason to split a database.

Do you understand the phrases: Normalization of data and Relational
Database?

I suspect your question will be answered by yourself if you understand
those two terms.

As a general rule, I would suggest that related or like data should be
maintained in a single database. Current Access versions will handle a lot
of tables etc and very large amounts of data until the total size approaches
2 gig.

--
Joseph Meehan

Dia duit

**Albert D.Kallal** · Jul 13 '06, 07:55 PM

Re: When to make separate databases?

"LurfysMa" <invalid@invali d.invalidwrote in message
news:indcb21mdf osldp1t25r9lmh7 7r8redual@4ax.c om...

>I would like to hear opinions on the tradeoffs of putting the tables,
forms, and queries for several related datasets in separate databases
vs one combined database.

You need to build one database that correctly handles the problem at hand.

You can't possibility consider modifying, and maintain several databases.
Trying to update, trying to even just train users as to which one to use for
what case, and building reports for EACH ONE is going to waste HUGE amounts
of money. This money can be used to feed the poor, or save $$ at your
company. You mean you write some code, and spend days updating some reprots,
and now you have to do the same for all other mdb files? This will not work,
and is gong to be a complete waste of time, money and human resources.

You need to define this problem correctly. Remember, accounting systems ,
job costing systems (for complex assembly), and contact management systems
allow
tracking of all kinds of information, and do so without having to have
different database for each case.

So, if you are asking if a integrated package is better then a
bunch of disjointed separate mdb files? The answer is that a well designed
integrated systems is much better. A single system allows you to operate on
all data at once. With separate systems, you can't run reports across the
data .Even just printing out simple mailing labels becomes VERY hard to do,
since you now have many different files all over the place.

The only
downfall of a integrated systems is that they more skill to design, but once
done, they are far more flexible and easy to use.

So, it seems easy at first to make a bunch of separate files, but then you
wind up with a spaghetti mess that is not maintainable. Your project will
thus die a slow and painful death as you begin to spend more time
maintaining all the separate systems, and you thus never get any work
done!!! You want to build a system, so when you come up with a solution to a
particular problem, it will APPLY TO ALL appcation at hand, not have to
re-make the problem for each case..

>
The advantages of separate databases as I see it are:
>
1. Smaller database files, easier to transport?

Well, how large to you expect this database to be? A few thousand records,
or in the millions?

I would worry about transport WAY WAY down the road. A 4.3 gig DVD burner
is less then $50 bucks. What is your time worth compared to cheap widely
avaible large capacity backup systems. Even a 1 gig usb memory jump drive is
cheap cheap these days. Hardware is so cheap, I would not worry.

Human cost and time = Great cost

Computer time, and computer cost/backup systems = very cheap and
affordable
today.

So, don't worry about transfer...worr y ALL ABOUT a good flexible design that
solves the problem. Backup and transfer can be solved WAY WAY down the road
after all is said and done. I would not loose one bit of sleep over this
issue...

2. Slightly easier to replace an entire db than a table within a db.

Well, again, I don't understand the above? Any well designed database is
going to have MANY tables. Are you suggesting to move each table into a
separate mdb file? How could you move data, or even know which mdb to copy
to antoher machine? Talk about making transfer difficult? What if you forget
one mdb file? --- way way easier to have a nice designed SINGLE mdb that
defines the problem at hand.

You move that mdb to another machine, and you are done!! Surly this is a
grand canyon of ease more so then trying to move/maintin a zillion mdb
files?

3. Simpler naming. No need to identify the subject.

What subject are you talking about? You mean it going to be easier to print
out a list of mailing labels from a zillion separate mdb files as compared
to one mdb file? How can you design a system where you say

"please print me a list of all subjects?

Worse is even trying to search for things, now they are strewn all over
several systems/files. I dare say this is not simpler at all, but is formula
for much pain and suffering.

4. Better for incremental backup. Changes to one subject would not
affect a huge database file.

Gee, a 100 gig disk drive is under $100. As I said above previously, this is
last of your problems, and you can throw heap widely available hardware to
solve
this backup problem.

The problem is not making a change to one subject, but
when you need to report, search, and work on MANY subjects!!

You are worrying all about he wrong things. The #1 worry IS TO GET THE
DESIGN right!!...every thing else in terms of optimizing, performance,
backup, etc. is a secondary issue that you can deal with EASILY down the
road....

Time to start reading up on normalizing your data.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

Shaw Communications

http://www.members.shaw.ca/AlbertKallal