[RESOLVED] Where should the database go?

[gwboolean]

Publishing/distributing an application truly confusing and complex. I am sure it is not supposed to be, but it is. I have finally gotten through the basics and, most of the time, successfully carry out this process. However, there are a couple of things I am wondering about, with regard to publishing/distributing a database application.

My applications are pretty simple and while they require a database, they do not require, nor are they well served, using a server-based database. It has been my practice to include the database as part of the package that I publish to load on another machine. However, I am beginning to see problems with that approach and am now considering that I might be better served to publish/distribute the application without including the database in the package and installing the database separately to the specific place called for in the application connection string.

I am just looking for pros and cons as to what would make a better approach.

[dday9]

To clarify, this is a desktop application? Also, what language are you using?

[gwboolean]

It is a deskstop planner application. The language is Visual Basic. The database is an Access database.

What I am after is the best approach, with regard to the database, to distribute the application and database to another machine. What I have done, in the past, is to load the database as part of the distribution package. I am not certain that this is a better approach than just distributing the application and separately loading the database into the location specified in the connection string of the application.

I have had mixed results with packaging the database as part of the distribution package. Additionally, I intend to update this application and I am not sure how I would do an update without affecting the data, using the distribution package containing the database. This is all way too complicated for me.

[dday9]

Which Visual Basic, .NET or legacy?

[gwboolean]

That would be .Net

Sorry to give this out piecemeal, but I really have no idea what is important for this.

[OptionBase1]

What is the database being used for? Is the end user taking actions that is resulting in changes being made to the data?

I know you want application updates to not mess with the data, but what if the application update includes changes within the database itself, such as new tables being added, or additional fields added to existing tables, etc? In that case the database does need to be "touched" at the very least for those changes to be made.

[Zvoni]

For filebased Databases (MS Access, SQLite), just ask yourself the following question:
Is the Data within the Database
1) just for the logged in User,
2) or should the Data be available to any logged in User?

In case of Answer 1: AppData\Local (or roaming)
In Case of Answer 2: c:\ProgramData (hidden Folder)

Or you do the "Deluxe"-Version: At first start-up of the program, ask the User, where to CREATE the Database!
(This implies you have all necessary SQL-Statements available to your Program at start-up).
In case of Answer 2, the chosen Location must be accesible to all users

SideNote: In case of "Multi"-User (more than 1 User (on different machines --> Network)) using the Database at the same time:
Use a DB-Server. Period!
Don't try to outsmart it by placing the Database on a Network-Share.
You're inviting trouble that's not worth it

[gwboolean]

What is the database being used for?

This application is a planner. The database is used to maintain records of tasks, contacts, et. al., information. The user would be able to add, delete and modify the data.

I know you want application updates to not mess with the data

Absolutely. I am also aware that a database might require changes, just as the application would. Which is something that seems so complicated that I am looking at the possibility of handling the database completely separate from the application for publishing/updating.

It seems to me that handling them separately would be relatively simple. In the case of this application, the number of users is so small, that I can easily update the user database without blowing their data out of the water.

Updating someone's database used to be a really, really big job and I am afraid that if I attempt to update the database, as part of the publish/update process, that things will go very badly for me. That word, "touch", scares the hell out of me. I can do a straight forward, by hand, update of a simple user database that contains data. But I have seen nothing providing any information about how to actually carry something like that out in the publish/update process. And I likely wouldn't understand it if I did.

[OptionBase1]

This application is a planner. The database is used to maintain records of tasks, contacts, et. al., information. The user would be able to add, delete and modify the data.



Absolutely. I am also aware that a database might require changes, just as the application would. Which is something that seems so complicated that I am looking at the possibility of handling the database completely separate from the application for publishing/updating.

It seems to me that handling them separately would be relatively simple. In the case of this application, the number of users is so small, that I can easily update the user database without blowing their data out of the water.

Updating someone's database used to be a really, really big job and I am afraid that if I attempt to update the database, as part of the publish/update process, that things will go very badly for me. That word, "touch", scares the hell out of me. I can do a straight forward, by hand, update of a simple user database that contains data. But I have seen nothing providing any information about how to actually carry something like that out in the publish/update process. And I likely wouldn't understand it if I did.

Based on what you've said, I would recommend keeping it as simple as possible, which may mean doing some things manually yourself.

With every process like this, there's a tipping point at which doing things manually doesn't make sense any more. But conversely, underneath that tipping point it can often not make sense to try to do a bunch of automated stuff, since the time and effort necessary to put in to automating and testing everything to make sure the automated process is bulletproof would only result in an actual time savings for you like 50 years down the road. There's nothing wrong with banging on other people's keyboards when necessary.

[gwboolean]

That is sound advice. I always have a tendency to want to do more than my capabilities or needs permit.