#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    Questions concerning a complex database system


    Hello,

    I would first like to start off thanking the people who run and manage and the members of the Dev Shed forums; you are saving my butt on this one. I owe you one!

    Here is what we are doing.

    Special Guests, Guest Booking Service for Television and Radio Talk Shows, currently has a calendar system within Outlook. It is integrated into a Microsoft Exchange Server so other members within the company can access Guest Calendars and manage them.

    We use a VBA programmer to program special Custom Forms (instead of the default Appointment form) that allow us to enter the details we need and also custom reporting script that intergrates into Microsoft Word to give us the information we need at a glance. It works OK, however it is very time consuming when you have to ask the Station for the information everytime and enter it, etc. We have definitely spent over $10,000 in a total of 2-3 years that it has been running and developing; however we do believe we have all the features we need for now.

    We are looking to revamp our entire system by allowing us to ditch Outlook, which can be cumbersome at times and ditch the manual entry of data. For example, a company called (which we use) Baconís Medias Source has tens of thousands of stations/networks/talk shows etc. within their database of which we can export and use, along with other valuable data such as how big the network/talk show/station is.

    If we had a system that would support this, we could implement an automated data entry system. For example, we type the first 3 letters of the stations call letters (like WZZM) and we could click Find or something to that effect and select the talk show we need or search for the information by other means to get the information we need within 5-10 seconds.

    Ok, enough for the intro; here is my real question. Which method of containing this database, and a GUI (web or computer based) should we use. What I had though would work is:

    MySQL database within our server (remote location)
    A web based solution that integrates with this database

    Simple enough, but not feasible; we also need to be able to manage the calendar system Offline (say a Laptop must leave the office). Therefore, I had though of this:

    MySQL database within our server (remote location)
    A web based solution that integrates with this database
    A Windows GUI that integrates with this database
    An internal database within the GUIís computer for offline data

    That sounds like an even better idea; it not only will allow for easy access outside of the browser, but also ability to work offline when away from the office and to be able to access the system from any location (without a GUI using web based).

    I am looking for recommendations on my options for this system; also I need recommendations for programming languages.

    Please keep in mind; we use a RedHat Linux Server (no special software) therefore we need something compatible with Linux, and we use Windows computers.

    I am basically looking for a little guidance in the right direction. To help you better understand our needs, I have attached a feature list sheet which includes MOST of what we want, but is still under construction. Please remember I have not intergrated my ideas that I have listed here into the feature set.

    Thank You for all of your help and God Bless!

    David Garza
    Attached Files
  2. #2
  3. No Profile Picture
    Vote Libertarian
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    N'wallins
    Posts
    276
    Rep Power
    12
    You say that you don't want a web-based front end because you may need to take a laptop off-site or offline. That doesn't make too much sense, because the information that needs to be accessed would be in the MySQL database, no matter what the front end. A windows GUI won't magically allow the laptop to connect to the database.

    I would store your information in the MySQL database, as you said, and then code up an interface in PHP, and use a web front end.

    What about the offline/offsite issue? You need access to the data, so you would have to take a copy of the database with you, no matter what the front end is. I've used replication with MySQL, and that could be a great way to go, or you could just do daily backups.

    Basically, I would figure out the max number of laptops that need offline data access, designate those as slaves under a replication scheme, and keep them on your in-house network. That way, when they're not being used offline/offsite, they will increase your data availability since you can query any of the machines, but only send writes/updates to the master.

    Then, when you need to go offsite/offline, bring down one of the slaves and take it with you. The slave will have a local MySQL, be running its own webserver, and you will be able to access the interface by pointing your webbrowser to `localhost`.

    A web interface can be developed much faster than a standalone program, and everybody already knows how to use one, so you can save on training costs.

    I am available for contract and can travel.

    Good Luck.
    • "Write programs as if the most important communication they do is not to the computer that executes them but to the human beings who will read and maintain the source code in the future" - ESR, The Art of UNIX Programming
    • "Programs must be written for people to read, and only incidentally for machines to execute." - Abelson & Sussman, SICP, preface to the first edition
    • "Programs must be written for machines to execute or else you just have a boring book" - DaWei_M
    • Vote Libertarian
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0
    Hello,

    I do agree, I had forgotten to mention that perhaps a constant synchronization for the mySQL database to be downloaded to all computers with a GUI (maybe 1 to 2 minutes out of sync at most) and therefore, when just pulling the Laptop you can carry it with you and when reconnecting it would check for changes in the local and remote database and sync accordingly.

    The GUI could possibly have a built-in scaled down version of mySQL (or install one when the GUI is installed) and also it could use the same interface as the web based front-end, eliminating the need for new training.

    Something else I had thought of, which would make it a bit more difficult, is to use a Java front-end (web site/local)enabling one page for all data, however that may not work on a PDA if needed to be used. Of course, a WAP system could be intergrated for a web based system as well.

    I would care to have your thoughts on this as well.

    Thanks and God Bless!

    David Garza
  6. #4
  7. No Profile Picture
    Vote Libertarian
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    N'wallins
    Posts
    276
    Rep Power
    12
    Originally Posted by CCF Hosting
    I do agree, I had forgotten to mention that perhaps a constant synchronization for the mySQL database to be downloaded to all computers with a GUI (maybe 1 to 2 minutes out of sync at most) and therefore, when just pulling the Laptop you can carry it with you and when reconnecting it would check for changes in the local and remote database and sync accordingly.
    That's exactly what replication achieves, except the lag is almost undetectable under normal circumstances. When a slave is brought down for any length of time, it automatically catches back up when it is brought back up.

    The GUI could possibly have a built-in scaled down version of mySQL (or install one when the GUI is installed) and also it could use the same interface as the web based front-end, eliminating the need for new training.
    No need to build it in or scale it down. Just run it separately and use an API for your language, or if it doesn't exist, make a wrapper for the C API in your language. The only advantage that I see to a GUI is that you can be more expressive with input controls. If you're going to mimc the web front end, why go through the trouble and expense to build and maintain a compiled program? PHP is a great way to interact with MySQL.

    Something else I had thought of, which would make it a bit more difficult, is to use a Java front-end (web site/local)enabling one page for all data,
    Not sure what you mean here. Do you mean that java enables you to use a single URL to perform many functions/reports? PHP does this as well.

    -------

    Again, maybe I'm biased since I make web applications rather than desktop applications, but I don't think you want to have to maintain the current (and future) feature set in two different languages. Perhaps you want to justify the expense of your VBA programmer?

    I think you hit the nail on the head here:
    Ok, enough for the intro; here is my real question. Which method of containing this database, and a GUI (web or computer based) should we use. What I had though would work is:

    MySQL database within our server (remote location)
    A web based solution that integrates with this database
    but then, here, I don't see how a Windows GUI alleviates the hurdle:

    Simple enough, but not feasible; we also need to be able to manage the calendar system Offline (say a Laptop must leave the office). Therefore, I had though of this:

    MySQL database within our server (remote location)
    A web based solution that integrates with this database
    A Windows GUI that integrates with this database
    An internal database within the GUIís computer for offline data
    I say forget the windows program. You don't want to have to maintain it. The issue is data access, and the answer is MySQL replication with the portables as slaves.
    • "Write programs as if the most important communication they do is not to the computer that executes them but to the human beings who will read and maintain the source code in the future" - ESR, The Art of UNIX Programming
    • "Programs must be written for people to read, and only incidentally for machines to execute." - Abelson & Sussman, SICP, preface to the first edition
    • "Programs must be written for machines to execute or else you just have a boring book" - DaWei_M
    • Vote Libertarian
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0
    You really do make some interesting points; the reason I mention the GUI is because setting up a mySQL server for each computer, laptop etc. would not be feasible because I am one person, LOL. Plus, I am 1200 miles from the main office where most of this will be needed.

    I am looking for a KISS solution; however that incorporates all of the features we need. Here is what we need for sure:

    A usable web front-end
    A mySQL backend database
    Offline capabilities

    Since we know our goal, I need to KISS it; to make things extremely simple.

    The simplest of ALL would be to have a program that will use the web front-end files (downloaded locally) and plain text database.

    Simple, YES; Efficient, No; Reliable; No

    If you are familiar with Help Center Live WinApp (http://www.helpcenterlive.com/winapp.php) than you should know what I mean or perhaps like the Google Deskbar (http://toolbar.google.com/deskbar/); they are basically a program that houses HTML files/sites and appear on the task bar as a little icon.

    If there was a way to install/setup/maintain a mySQL server where a trained monkey could, I would be in favor for it.

    I do agree with you; a GUI is NOT necessary but a way to work offline with the data in the DB is. The local database is a must; I wish I could find the best solution for that.

    That is where I really need input on. If there isn't something out there, I am afraid someone may have to develop something that will work for us.

    I am curious though, what do other businesses that support the KISS idea do in this situation?

    Thank You and God Bless!

IMN logo majestic logo threadwatch logo seochat tools logo