Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

When to make separate databases?

Discussion in 'Information Technology' started by LurfysMa, Jul 28, 2006.

  1. LurfysMa

    LurfysMa
    Expand Collapse
    Guest

    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?

    --
    Running MS Office 2000 Pro on Win2000
     
  2. Loading...

    Similar Threads Forum Date
    Sexes Sitting Separately New to Gurdwara Apr 18, 2013
    India Bill for Separate Status to Sikhism Today Breaking News Aug 24, 2012
    General Home Ministry okays separate law for Sikh marriages Hard Talk Dec 23, 2011
    USA US Group Seeks Separate Registration of Sikh Marriages Breaking News Sep 2, 2011
    India Centre Drops Proposal for Separate Sikh Marriage Act Breaking News Aug 30, 2011

  3. salad

    salad
    Expand Collapse
    Guest

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

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

    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.
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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
     
  5. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    "LurfysMa" <invalid@invalid.invalid> wrote in message
    news:indcb21mdfosldp1t25r9lmh77r8redual@4ax.com...

    >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...worry 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!!...everything 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
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     

Share This Page