The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Install with empty DB or create DB in code?

Is it better to create the database structure on my PC and include it into the installation package or should I rather create the DB in code, when the application is run for the first time?

So far, I have included the DB into the installation and when there was an upgrade, only the new fields have been checked/added to the tables.

Now I was thinking if it would be a better idea to create the whole database in code at startup:

1) Check if the DB exists. If not, create it.

2) Check if all the tables exist. If not, create them.

3) Check if all the fields in all tables exist. If not, create them.

What do you think?

Petr Veit
Tuesday, March 11, 2008
Make a small db initialization script in a .sql file together with your code.
Scimore Send private email
Tuesday, March 11, 2008
just remember with vista, you need to create all files outside of the install in the user profile. if you want to create a skeleton db in the /program files/app dir that would work, then just copy the skeleton to the user profile...

Tuesday, March 11, 2008
I think it all depends on the skill levels of whoever is going to be installing the thing.

Certainly for "enterprise" applications a separate script is likely to be preferable as this can be handed to release managers and DBAs to do their stuff - they are likely to want to evaluate whether changes to the database structure will break any integration points or reports.

For simpler apps doing it "automagically" might be the right thing to do.
Tuesday, March 11, 2008
Thanks for the comments.

This will be a simple desktop application with simple local Access or SQL Server Compact Edition database (or anything similar).

It will be used by people from agriculture, so don't expect any PC skills. The installation should be automatic from one setup file.
Petr Veit
Wednesday, March 12, 2008
Do you need to upgrade existing databases in the future? Or can you just delete/overwrite any existing DB's when releasing a new version? If not, you would need scripts to upgrade existing tables/data to new version anyway, and so you might as well start your scripts from a blank database, and then just add your upgrade scripts to the end of the script. With some comments and logic in you scripts, that senses if a DB exists, and if so what version, you will be able upgrade from any version to the version getting installed.
Wednesday, March 12, 2008
Sounds like you will be complicating the issue.

I just created a SSCE3.5 DB and add/edit the fields whenever I need to via code, no SQL scripting required at all.

Works great for me.
glen harvy Send private email
Wednesday, March 12, 2008
Have the app create the schema from scratch if at all possible. That's as idiot proof as it gets. Only the app really knows for sure what schema the app needs. Anything else like a install procedure or an install program might get out of sync due to development or deliverable snafus.

Consider creating one field at a time with 'if not exist' logic. Nice and idempotent. That way if there's a pre-existing database from an older version of the app you have a good shot at preserving the old data, that is if you don't gratuitously break backward compatibility by renaming old fields or something stupid like that. Don't count on the user to have good backups and to know how to restore.
Wednesday, March 12, 2008
Yes, there will be some uprades in future and the old data must be kept.

It seems that I will try to create the DB in code. If there will be a new upgrade, the application will check if the newly added fields exist and will be created if needed.

So far, I always added some new fields in the upgrades. I will see how it will work when changing field types, adding tables etc.
Petr Veit
Wednesday, March 12, 2008
When you state you would include the database structure and include it in the install do you mean you will be distributing a binary database file for Access or SQL CE?

Unless you're also packaging the runtime environment I wouldn't do that. I've developed installers for client/server databases and we only did that when we embedded the database engine. It is easy today when you select version x.y.z of the database environment as your target and life is good for 18 months. Then there is a major update to the database environment. Followed by a new OS release in 3 years and your customers are trying to install on that. You will be adding more and more binary database files (and more conditions) to your installer to keep covering the additional targets.

Our current installer uses scripts to initialize the database and then the application applies schema updates on startup. The main reason for the script initialization is it is easier for the installer to verify the database connection (verify remote server, network/firewall, sockets open, authentication, permissions, create users, setup backup jobs) than to make the application do it. If the installer succeeded then the database was working.

However the installer was a non-trivial project (written in the old Installshield Multiplatform, heavily scripted with custom dll/so/jar files) because it targeted about 6 different DBMSs across Linux/Solaris/Windows.

If your application can find the database engine then create and install the database on the target machine then do it that way. Your database information is in one place and you are more independent of the target environment. Also have one less piece of the installer to maintain.

And those 18 months fly by very quickly. It seems like the installer is just finished and someone has just installed their OS upgrade and wants support.

jz Send private email
Wednesday, March 12, 2008
Thanks for your reply jz.

The application will be used by people who don't even know what a database is. That's why I want to use something simple:

1) Access support is directly in Win2000 and up, which would be OK.

2) For SQL CE, I hope I can add the installation of the engine directly into my application setup. If my customers had to install the DB support separately, it would be unusable for them. I've read somewhere that SQL CE engine is just some DLLs, but I am not sure.

Yes, I thought that I would include an empty database file (MDB or SDF file) into the application setup. I have done this in the past and it worked.

Thruth is that if the application creates the DB when it is run for the first time, it is maybe better. When the user installs new app version, the old DB will not be overwritten with an empty DB. Instead, the DB will be checked at startup and all missing fields would be added.

Thanks again,
Petr Veit
Thursday, March 13, 2008
Whichever approach you use, just make sure to put the actual data files in the user's home directory:

Win 98/ME/2000/XP:
  \Documents and Settings\%USERNAME%


Don't hard-code the "C" drive either, since differnt systems may have users' home directories on a different drive or partition.

If you put your DB files down in the "Program Files" hierarchy, you'll be very sad when someone eventually installs your software on a Vista limited-user account, and your software doesn't work anymore.
BenjiSmith Send private email
Friday, March 14, 2008
BenjiSmith: all my apps are XP/Vista compatible, so I am already using this approach.
Petr Veit
Friday, March 14, 2008
Just to add my two cents here - I would be doing a combination of both.  In that I would have a script that runs on startup, which checks to see if the db is installed to a particular version. There would be a table in the db which stores the current version.  The 'latest' version number could be read from the 'latest' script file name.  You would name your db scripts script.01.00.sql or something like that.  If there is a later script file than the currently stored DB version, it will run in that script and update the DB version.  The first time you run your app, a query would check for existence of the version table before even attempting to find the version.

This approach gives you a couple of advantages:
- if your app is to be installed in a corporate environment where a DBA wants to control the database separately, they can run the db script in before your app is installed.
- if you have a user who just wants to install and run, that will work too
- it gives you a 'move-forwards' structure so that when you have inevitable patches and new releases you can just release a db script file with the patches in it.  The 2.0 version might add columns to a table, or drop a table and replace it with something else.

Sounds like a lot of work up front, but it will save you a lot of time and trouble down the track when you have to start issuing patches for bugs.  The golden rule of commercial software is 'build to maintain'.  If you're going to make money out of it, it's going to be around for a while, and people are going to expect improvements over time.
Bruce Chapman Send private email
Wednesday, March 19, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz