Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Databases Reorgization or Reimplementation

    I have the task of reorganizing or reimplementing a system of databases to allow for efficient data mining of information in addition to tasks already being performed with by using the databases.

    Currently:
    All databases are of Jet 4.0 (what Microsoft Access usually uses) databases managed using Microsoft Access 2003. The database storage files are of "Microsoft Access Databases (*.mdb)" stored on a central location (server).

    Over 1000 databases exist (rough guess is about 1,500 databases), each having many tables and forms.

    On average, about 10 clients access the data at a time with twenty or less total clients that may access the data.

    I am thinking an SQL database solution with an easy to use front-end (this would probably be Microsoft Access) may be what I need.

    What database system would be recommended for this type of project?

    Is it possible to query multiple databases for a given data at a time (this may be useful for the data mining)? Perhaps with SQL?

    Is it possible to create a database of databases?

    I am still new to database design and my experience has mostly been with using Microsoft Access (mostly Microsoft Access 2000) for table and form design. I am open to trying new, efficient systems and would appreciate any assistance I could get.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Ascii2
    I have the task of reorganizing or reimplementing a system of databases ... Over 1000 databases exist ... I am still new to database design ... would appreciate any assistance I could get.
    From the little you've mentioned I'd say you have a difficult task ahead of you. If all the databases are the same structure then I'd try to combine them into one single database. If they're not the same structure then I'd suggest looking around for another job. In either case I'd strongly consider getting someone on board who knows what they're doing.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    This does sound like an almightly mess, which is probably typical of companies that are blighted with a legacy of monolithic desktop systems like Access. First and foremost I'd say you should look for some help. Hire someone if necessary. You need someone with experience in client-server database development and data integration.

    As Mike says, your task will be easier if all databases are exactly the same. Sadly they probably won't be, even if you think they are.

  4. #4
    Join Date
    Apr 2009
    Posts
    7
    Quote Originally Posted by mike_bike_kite
    I'd strongly consider getting someone on board who knows what they're doing.
    This I intended and still intend to do.

    I want to be sure I get a truly qualified person to work with me on this project. What are some good sources for well-skilled people for this task?

    Any specific recommendations for the Chicago, Illinois, United States of America area?

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Ascii2
    Any specific recommendations
    No doubt you'll get loads of offers now.

    Before you start though I'd be quite carefull to decide what it is you actually want from this new system, is it:
    • just to clean things up
    • just to standardise everyone making things easier to manage
    • savings due to economies of scale
    • to offer new features
    • to combine the best of all your existiing systems

    Will the benefits of this new system outweigh the costs of implementing it?How are you going to get existing users to make the switch? Will they want to switch? Can you force them? Your original email didn't mention any desired benefits so will your users just see this change as change for change's sake?

    Would it be eaiser to comming up with a new system that offers more features etc and then get people to migrate accross? You could provide tools to help users migrate data in. Then do a few groups at a time. Get their feedback and improve the system. Before moving more groups across and closing down their old systems.

    Mike

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'd echo a lot of that.
    I think it would be interesting to know an approximate split of these 1500 databases. Are they are for the same thing, are they 1500 completely different databases, are there about 10 different models of about 150 databases each etc.

    How many distinct business functions are being supported by these databases is going to be a very major factor in converting them. It may be that some are not worth the effort, whereas others are (depending on your thoughts on some of the above answers).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2009
    Posts
    7
    Quote Originally Posted by mike_bike_kite
    No doubt you'll get loads of offers now.

    Before you start though I'd be quite carefull to decide what it is you actually want from this new system, is it:
    • just to clean things up
    • just to standardise everyone making things easier to manage
    • savings due to economies of scale
    • to offer new features
    • to combine the best of all your existiing systems

    Will the benefits of this new system outweigh the costs of implementing it?How are you going to get existing users to make the switch? Will they want to switch? Can you force them? Your original email didn't mention any desired benefits so will your users just see this change as change for change's sake?

    Would it be eaiser to comming up with a new system that offers more features etc and then get people to migrate accross? You could provide tools to help users migrate data in. Then do a few groups at a time. Get their feedback and improve the system. Before moving more groups across and closing down their old systems.

    Mike
    The benefit of having a new database system should allow cost savings and facilitate data mining (it is quite time consuming finding related data from different Access databases).

    The benefits should exceed the costs. I have not yet fully been able to estimate the costs (Much of the cost may be dependant on time, and I am uncertain how long an implementation would take).

    I do not know whether it would be better (I am not looking for easier) to incrementally migrate to a new system without being able to closely estimate the time for project completion.

    New features may be desirable, especially if they can significantly reduce the costs (time) related to the performance of tasks.

    Employees are professional and may be trained to use new features. They may welcome a new system that they perceive may benefit them (decreased task time would be the main benefit). If the new features are many and unfamiliar, it may be best to train in the use of the new features slowly.

  8. #8
    Join Date
    Apr 2009
    Posts
    7
    Quote Originally Posted by pootle flump
    I'd echo a lot of that.
    I think it would be interesting to know an approximate split of these 1500 databases. Are they are for the same thing, are they 1500 completely different databases, are there about 10 different models of about 150 databases each etc.
    I do not have the split information at this time.

    I believe There are few models (maybe about 3).

    Many of the databases are similar (for example, each project may have the same fields (each project starts with the same model)).

    Most of the databases I believe are prject related databases.

    Some logs are entered into Microsoft Excel 2003 spreadsheets.

    I believe most of the databases are projects

    Quote Originally Posted by pootle flump
    How many distinct business functions are being supported by these databases is going to be a very major factor in converting them. It may be that some are not worth the effort, whereas others are (depending on your thoughts on some of the above answers).
    I believe that no more than four business units use the databases (directly or indirectly).

    All the different business units use the same databases. New data is also added frequently to the databases.

    Reoprts from information from the databases are to be generated. A tool currently is Seagate Crystal Reports.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I wouldn't underestimate the issues of getting all your users to change over to a new system and away from something they're used to. I obviously don't know the structure of your company but from experience I'd say that everyone hates change, especially if the only people getting a benefit from the new system will be the people in the central office.

    You could run a simple survey around the current users to see how the system could be improved while promoting the benefits of this new system. Then try and use any reasonable suggestions in the new design. In this way they might feel a little bit more behind the project.

    Mike

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    In this way they might feel a little bit more behind the project.
    I would also echo this. Stakeholder buy in is as big a factor in the success or failure of a system as the technical proficiency.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    True.

    One approach might be to migrate the data into a back-end server, but keep the access front end, (forms, reports) and link the front end to the back end with ODBC.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Apr 2009
    Posts
    7
    Quote Originally Posted by loquin
    One approach might be to migrate the data into a back-end server, but keep the access front end, (forms, reports) and link the front end to the back end with ODBC.
    This what I thought would be best (although with more than Microsoft Access as a front-end).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •