Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    High level concept discussion

    Hi all,

    Just need a general high level discussion to see if my thoughts are 1. workable 2. practical and 3. if there are better alternatives.

    Firstly my background is in project management, so whilst I am by no means technical I do have a minor understanding when it comes to databases, networks, communications etc.

    Secondly, my latest muse/project is hampered by budget i.e. there isn't one and hence will more than likely be built by myself. Historically I have built and worked with a lot of access databases, plus also used and maintained sql databases (although have always had a DBA to build, deploy, upgrade etc.). I am willing to learn new systems however the reward has to be evident to warrant the effort I will have to put in. Confident with vba, stored procedures, php, you get the idea - all the basic stuff and none of the trained highly technical stuff.

    So on with the project. Looking to create what I refer to as a star system whereby the master db is located at head office, this updates or creates local databases for branches, and users interact with their closest db through a hand held device (more than likely some android based app but that doesn't need to be specified just yet).

    Fairly simple so far, however the user interaction isn't just for reviewing, it will also be for updating so I end up looking at a diagram whereby updates to the underlying data can enter the system at multiple points and could conceivably conflict or duplicate. Easy to resolve in a modern connected world where data can be instant but I have some locations that have no connectivity to the outside world and will rely on manual updates - hence the issue.

    I am reminded of a MS Access solution which I helped to develop back in 1997, which had three installations around the world and would ftp updates to the master destination as configured and then ultimately publish if it was a publishing site.

    Fast forward 15+ years and is MS Access still the best option for me? Or is there something out there that will achieve what I need without the effort of building a bespoke access db with all the self checking etc etc.

    All thoughts gratefully received and if I haven't made anything clear then feel free to question me, its how we learn after all.

    Thank you,
    Brad

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Resolving the conflicting changes with disconnected databases is always a challenge. A cloud solution (AWS, Azure, Google Compute, etc.) would be best if your remote sites can connect to the cloud via the internet.

    Without a budget you may not be able to use commercial products, but Microsoft SQL Server Merge Replication is exactly what you've described. If you can afford the licensing (for schools and non-profits this could be no charge), it meets all of the needs that you've described.

    If you can't use that answer, then I'd probably use a simple approach where each location has both a "local" and an "enterprise" database. Both databases are only updated by their respective "owner", the Enterprise database is "owned" by a central site and the "local" database in each location is owned by that location. At the central site you have the updatable copy of the Enterprise database and separate read-only databases which are each a copy of one of the "local" databases.

    If you write your code so that each one updates only one database (either their own location or the Enterprise), the system stays simple and is easy to implement and manage. They can exchange data any way that you see fit (which is especially important with your remote location(s) with expensive, slow, and intermittent connectivity. I would recommend that you plan on occasional refreshes that use a complete backup/restore which limits the effect of any glitches and ensures that any accidental changes to someone else's database are short lived.

    MS-Access wouldn't be my first choice. I'd prefer to use a server based approach. If you need to be completely independent, this can be relatively cheap and easy using a web based app and database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2016
    Posts
    2

    RE: High level concept discussion

    Thank you for your input Pat

    I'll look into the MS SQL option as that could be possible.

    Your local vs enterprise database option is interesting and whilst I had thought about doing this with the data my brain hadn't made the logical step of separating the databases, so thanks for that. I can easily dictate which site owns specific pieces of data and whilst a little messy, it does indeed fit the bill.

    I assume a server based approach would be easier to migrate from/to when a site gains access to the internet (or just connectivity of some kind) so that would be an advantage over the Access solution. Are you able to suggest or recommend any web based apps and databases? Also I am assuming that these would work on a local server, which would enable the non connected sites and the connected sites could benefit from 'centralisation' so to speak.

    All the best,
    Brad

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The logical/physical separation of the databases takes a general concept (the "ownership" of the data) and gives it a physical form that makes it simpler to ensure and enforce. It allows the easy backup by the "owner" and restore by the "user" to remove a lot of the day-to-day complexity from using the system as a whole. Different database products like DB2, Microsoft, and Oracle allow an advanced DBA to implement this within a single database, but by keeping the databases distinct it makes it easy for your folks in the field to do this too without a lot of training or automation.

    The choice of platform for your development and deployment ought to be based on the tools/skills/costs for your system. Open Source offers many options. Microsoft offers a great (and unified) platform. There are other products that will fit your needs too. This problem has WAY too many options for me to give you a decent opinion without a LOT more insight into your organization, its skill base, the cost and availability of tools, etc. Pick any set that works for you, if you are comfortable with a tool set it is almost certainly able to do the job and the comfort ensures that the tool set will be efficient for you and your team.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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