• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

MySql help

Hi guys,
I just need to find out the right database architecture before developing an application either in VBA in Ms Access or VB6.
This is for a data cleaning software where the user needs a copy of the database from the live server and the application runs the queries of a local copy of the database.
This is how the application works now.
•      We are using a local MS Access 2007 database to import selective tables from the database servers. There is a data sync feature on the application that does this importing
•      There is a java applications that runs its queries from the local Access database
•      All the tables in the local database is a copy of the required tables from the live database except for two which are linked tables. The linked tables are in place so that the users don't work of the same set of online data.
The issue that we are having now are:
•      java application doesn't come with inbuilt classes like VB or other Microsoft products for filtering, tables, etc. Although some of  these have been achieved in the existing application, the performance is unsatisfactory.
•      The local access database gets corrupted often as we sometimes deal with 30k+ lines of data which queries the local database around 10 times per line

We use Mysql as our RDBMS. The solution we possibly are looking for:
•      Is it possible to create a local copy of the live MySql database (only selected tables) in mySql or any other RDBMS similar to MySql
•      The key element of having a local copy is to sync the database both ways. any changes made to the local DB should be reflected in the live DB as well. It doesn't have to be at run time. A trigger from the main application is good enough to do the job
•      Please bear in mind that we are planning to develop the main application using VB oriented language
•      and the most important thing is do we really need to have a local copy of the database? the only reason we were doing it was the query that were running on the old application were taking forever to complete. Perhaps, master- slave architecture is the way to go ( don't know much about it)

Looking forward to hearing from you experts

Thanks
0
Clement P
Asked:
Clement P
  • 6
  • 3
1 Solution
 
EyalCommented:
I don't think you need to save local database. it adds very complected sync work. if you build your database good queries shouldn't take more then 2 seconds to return result.

saying that... I would do it this way:

1

redesign the rdbms database to meet the new goals

2

create database repository - some kind of web service that will handle front requests

3

create front - can be whatever you want VB,WEB,Java...
if you have any more question i'm here
0
 
Clement PAuthor Commented:
hi,
sorry for my late reply but can you please elaborate on step 1 and 2 please?

Thanks
0
 
EyalCommented:
Hi,

#1 a query shouldn't take so much time (as you mentioned). this happens usually when database design is not met to the requirements. So this was mentioned so database design should be considered also in the process of rewriting.
#2 usually in each good application we have several layers. UI(#3), BL(#2), DAL, Database. this section is should be built in conjunction to the UI you select. for example: if you decide to build web application that will have reach GUI using browser (web2). you will have to do many requests for each page using ajax to get the relevant data to display in the page. and that ajax handlers are the repositories I mentioned. of course this layer can also be break down further more
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Clement PAuthor Commented:
Hi,
Again apologies for not getting back to you earlier on this. We are more inclined to use a local version of the database as some of the users of the application are connected remotely through VPN and the connection is not that great. So having local database is always handy if the connection goes down.

so, is it possible to have a local copy of MySql to use as the backend DB?
if yes, is there a way to sync both ways at run time? If syncing bothways at run time is an issue, the syncing can be triggered from the application.

Please give me a buzz if you need more clarification

Thanks
0
 
EyalCommented:
0
 
Clement PAuthor Commented:
Thanks sorry was away on a holiday , will check and come back to you guys thanks
0
 
Clement PAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for groznywozny's comment http:/Q_27388843.html#36998822

for the following reason:

That helped me a great deal to go in the right path thanks again
0
 
Clement PAuthor Commented:
Comments by EVal helped me to go in the right patch , i want to accept solution and close the case.
0
 
Clement PAuthor Commented:
Thanks for the help
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now