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.

Selecting database for app...

I have developed an Inventory and Invoicing application using VB.NET and MS Access. I opted for MS Access because its MDB file can easily be copied anywhere. Recently I'm getting orders for big traders where the data size can grow considerably. I therefore want to use another small footprint but powerful database for my app.

I have Oracle 10g XE, PostgreSQL and MySQL in mind. SQL Server 2005 XE does not support VB.NET 2003. Oracle 10g XE is very heavy. Most of my clients are having 256 MB RAM. PostgreSQL and MySQL are great but most of the times help is not available from forums. We don't have many private consultants of both in our region. Please suggest how to proceed.
K Send private email
Sunday, December 03, 2006
 
 
Firebird (& IbExpert design & admin tool). Excellent, reliable, powerful, effective.
Mitch
Sunday, December 03, 2006
 
 
Second firebird, easy file copy, name the extension what ever you want, single dll install, or superserver, runs on linux or windows, super fly weight, hard to impossible to corrupt, Jim Starkey was The Man!

I would reccomend database workbench however for the tool to interface to firebird, http://www.upscene.com

Sunday, December 03, 2006
 
 
"SQL Server 2005 XE does not support VB.NET 2003."

Really?  How come?

That seems like the most logical choice, why doesn't it work with VB.NET 2003?
Almost H. Anonymous Send private email
Sunday, December 03, 2006
 
 
What kind of "big trader" has 256Mb of RAM?
David Aldridge Send private email
Sunday, December 03, 2006
 
 
"SQL Server 2005 XE does not support VB.NET 2003. "

Methinks you've got some wires crossed here.  SQL Server 2005 XE can be accessed via all the standard methods - ADO.NET, OleDB, ODBC, etc.

If you're talking about actually running code inside the database, you're correct that it doesn't support .NET 1.1.  But you'll not have anything like that with any of the alternatives, either.
Posted by me
Sunday, December 03, 2006
 
 
"I have developed an Inventory and Invoicing application using VB.NET and MS Access. I opted for MS Access because its MDB file can easily be copied anywhere. Recently I'm getting orders for big traders where the data size can grow considerably. I therefore want to use another small footprint but powerful database for my app.

I have Oracle 10g XE, PostgreSQL and MySQL in mind. SQL Server 2005 XE does not support VB.NET 2003. Oracle 10g XE is very heavy. Most of my clients are having 256 MB RAM. PostgreSQL and MySQL are great but most of the times help is not available from forums. We don't have many private consultants of both in our region. Please suggest how to proceed."

First of all, you have no idea of the "footprint" difference between SQL Server 2005 Express Edition and Oracle 10g Express Edition.  You're talking a 200MB difference if the database is being used with any amount of frequency.  Oracle's Express database has perhaps the largest footprint of all the available Exprss Editions from commercial vendors.

IBM DB2 9.1 Express-C is perhaps the best Express-level database product.  You have no database size limits, no throttle.  The only things you don't have are the ability to access more than a few Gigs of RAM and more than 2 processors.  Its footprint is higher than SQL Server Express.

If you think the data size can grow considerably, choose IBM.

They have the best Visual Studio 2003/2005 integration of any database vendor (even Microsoft), excellent documentation, and an Eclipse-based development tool that rocks.

There are drivers for basically every data access type on Windows (JDBC, ODBC, OLEDB, .NET 1.1, .NET 2.0, etc.).  DB2 can also store, access, and manipulate relational data as XML natively, which is a big plus :)

If you want it to be really lightweight, Firebird may be your best choice.  I'm not into distributing MySQL/PostgreSQL to customers, because they are obscure products and you may end up having to "support" them because they are part of the solution (installation problems, etc. etc.).

- Nate.
Nate Send private email
Sunday, December 03, 2006
 
 
>> What kind of "big trader" has 256Mb of RAM?

Even today, most of the large applications (Financial Accounting and Inventory) were developed using Foxpro for DOS (in our country). So even traders with large scale supply and purchases are still using Pentium 133 MHz or a maximum Pentium II machines. Those who are willing to shift to a Window based app. is just because the data of the Foxpro app (including the app. itself) is not secure. Foxpro deCompilers are available widely available on the net.

For a Inventory/Invoicing app., a maximum 256 MB RAM is sufficient.
k Send private email
Sunday, December 03, 2006
 
 
>> Firebird (& IbExpert design & admin tool). Excellent, >>reliable, powerful, effective.

Does FireBird support Crystal Reports?
k Send private email
Sunday, December 03, 2006
 
 
I still don't understand why SQL Server Express is not an option.
Almost H. Anonymous Send private email
Sunday, December 03, 2006
 
 
SQL Server 2005 XE displays an error: "This version of SQL Server 2005 may not work properly with this version of Visual Studio." when tried to view Server Explorer in .NET 2003. This is the only problem

Monday, December 04, 2006
 
 
I would look at SQL Server CE (formally SQL Server Everywhere)  I't lightweight (1.4 megs) runs in process and supports full synchronization with SQL Server.  It has a limitation of 4 Gig file though.  If you are getting databases bigger that that, I don't think a client side database is the best choice.
Chris Hoffman Send private email
Monday, December 04, 2006
 
 
>> Does FireBird support Crystal Reports?

Better question is "does Crystal Reports support Firebird?".  Answer: Yes.  Use ODBC.
FirebirdUser
Monday, December 04, 2006
 
 
"'This version of SQL Server 2005 may not work properly with this version of Visual Studio.' when tried to view Server Explorer in .NET 2003. This is the only problem"

Ok, so it's not fully compatible with the *IDE*.  That doesn't mean it's not compatible with the language.  I highly doubt any of the other database packages listed are compatible with the Visual Studio IDE either. 

You're making your life more difficult by not simply going with the logical all-Microsoft solution.
Almost H. Anonymous Send private email
Monday, December 04, 2006
 
 
Still missing the point, however, that SQL Server 2005 Express Edition has hard-coded limitations on database size (they removed the workload governor from MSDE, though, which is good), so if your database grows to larger than 4GB (which is highly likely if the application is meant to be used with alot of database activity over a long period of time) it will be insufficient.

It's a nice competing product for web applications, but not the most optimal solution for deployment with those types of applications.  It is meant to be a replacement for Microsoft Access (and who would want a 4GB+ Access database /shivers/).  If Access can't do the job, I would look over SQL Server 2005 Express, Oracle XE, and any other database that imposes such limitations.

Firebird is probably your best choice, because of its small installation size, features, speed, and ability to be embedded (read the docs for limitations regarding the use of an embedded server, you may not like that idea).

If you are worried about people using suboptimal (older, deprecated) hardware and machine setups, then SQL Server 2005, Oracle XE, and DB2 are all a no-go, and Firebird is your best solution given the aforementioned reasons.  Oracle is a resource hog, DB2 is also (like everything from IBM), and SQL Server 2005 requires .NET 2.0 (which will only install on SP4 Win2k and WinXP machines).  Firebird can run on almost any version on WIndows NT/9x (as a service on NT, and as an application on 9x since they don't support services).

Firebird lacks documentation, and I don't think the InterBase 6.0 docs are a suitable substitution becasue they have been migrating away from that version for years now.

- Nate.
Nate Send private email
Monday, December 04, 2006
 
 
Also, DB2 allows you to store, retrieve, and manipulate relational data as native XML (not transforming it to XML like SQL Server, but store as XML IN the database), which can be really neat :)

I always found MySQL too quirky (like different table types, having to sacrifice speed for other abilities available in more capable database systems).  PostgreSQL is good, but I haven't really ever used it beyond installing it on my Linux distro and then deleting the VM.

- Nate.
Nate Send private email
Monday, December 04, 2006
 
 
"Still missing the point, however, that SQL Server 2005 Express Edition has hard-coded limitations on database size"

At which point you upgrade to the full version.
Almost H. Anonymous Send private email
Monday, December 04, 2006
 
 
What about Sybase iSQL Anywhere? They have also released a FREE version. I installed and used it. It is feature rich and has nice front-end for management.
K Send private email
Monday, December 04, 2006
 
 
You might also want to look at Cloudscape by IBM:

http://www-306.ibm.com/software/data/cloudscape/
*myName
Tuesday, December 05, 2006
 
 
For embedded RDBMS, nothing beats SQLite.  I've the same need for embedded DB a while back and I researched a number of options.  Firebird was a close second but I picked SQLite at the end.  Don't let the name "Lite" fool you.  It doesn't mean it's lite on features or capability or performance.  It's quite a powerful database system.  The "Lite" is referring to its small footprint and low system requirements.

Among the pluses:

- Low Cost: free.
- Liberal License: public domain, with source code.  Beats GPL and the variants.
- Small Footprint: couple hundreds K, both in memory and on file size.  Beats pretty much everything out there.
- Huge Datasize: 2 tibibytes (2^41 bytes) max.  In practices up to TBs of data.
- Low Maintenance: zero configuration
- True RDBMS: supports full transactional ACID (even MySQL can't claim that without InnoDB)
- Fast Speed: comparable if not way faster than other DBs in various tests.
- And many more.
fh Send private email
Tuesday, December 05, 2006
 
 
Come on, are there any database platforms out there that haven't been suggested yet?
Almost H. Anonymous Send private email
Tuesday, December 05, 2006
 
 
Unfortunately you can't expect decent performance from a powerful database running on the machine with 256 MB of RAM, especially if your application is running there, too.  SQLite will work, but I don't see why it is any better than Microsoft Access for this particular task.  You know, if it's not broken, don't fix it...
Jeff Zanooda
Thursday, December 07, 2006
 
 
"At which point you upgrade to the full version."

Missing the point, however, <g> that other databases allow you to use equally intuitive tools (some better aimed at developers) without hard-coded database size limits and hefty upgrade costs, than SQL Server 2005.  The worst thing you can do to end-users is make them depend on a third party application (that is VERY expensive) for operation of your application [i.e. once their database is > 4GB, if that should ever happen, they will HAVE to upgrade to a paid version of SQL Server, which comes with many other costs and obligations, for your program to use that database).  You'll also have to make sure you application is not hard-wired to use a named instance of SQL Server 2005 (what many vendors who distributed MSDE did), or the upgrade can break it.

Also, installing SQL Server 2005 will stop any application depending on SQL Server 2000/MSDE from installing if those databases must be installed.  SQL Server 2005 doesn't allow MSDE to install *after* it's been installed.  This can cause unnecessary headaches for users.  You will get bitched at, even if it isn't your fault, because of the choice of database that you have made.

Use something more foolproof, IMO.  I would personally go the Firebird route, since't it has a small install size, uses little resources, and can be embedded.  The database is all 1 file, which is also nice.

- Nate.
Nate Send private email
Saturday, December 09, 2006
 
 
"For embedded RDBMS, nothing beats SQLite.  I've the same need for embedded DB a while back and I researched a number of options.  Firebird was a close second but I picked SQLite at the end.  Don't let the name "Lite" fool you.  It doesn't mean it's lite on features or capability or performance.  It's quite a powerful database system.  The "Lite" is referring to its small footprint and low system requirements."

I believe I came into problems with SQLite when it came to multiple users accessing the same database, where it failed miserably, IIRC.  It's been a while, and I've been keeping an eye on it, but nothing to talk about.

Maybe try Turbo FlashFiler, which is now MPL 1.1 and on SourceForge.  It's Delphi/BCB only, though, but works great.

- Nate.
Nate Send private email
Saturday, December 09, 2006
 
 
Nate, (and if anyone else is interested)

If anyone is looking to find Firebird documentation, take a look at "The Firebird Book: A Reference for Database Developers" by Helen Borrie.
IanH. Send private email
Monday, December 11, 2006
 
 
> I came into problems with SQLite when it came to multiple users accessing the same database, where it failed miserably.

In SQLite documentation "High Concurrency" is listed under "Situations Where Another RDBMS May Work Better".

http://sqlite.org/whentouse.html
Jeff Zanooda
Monday, December 11, 2006
 
 
"Nate, (and if anyone else is interested)

If anyone is looking to find Firebird documentation, take a look at "The Firebird Book: A Reference for Database Developers" by Helen Borrie. "

The problem with Open Source/Free Software is essentially that.  They totally neglect documentation in hopes of one good book being written.  I do not wish to buy a book to use a database.  I personally don't use the Firebird database, myself.  I use DB2 Express-C for my needs, and it has excellent documentation in the form of Information Center (basically Books Online).

"In SQLite documentation "High Concurrency" is listed under "Situations Where Another RDBMS May Work Better"."

Yes, I know this.  I read documentation.

- Nate.
Nate Send private email
Saturday, December 16, 2006
 
 
Nate:

"I believe I came into problems with SQLite when it came to multiple users accessing the same database, where it failed miserably, IIRC."

The OP clearly stated he wanted an embedded database solution in a low resource setting.  You really don't want to have concurrent multiple users accessing an embedded database.

To have good concurrent multi-user support, you really want to have a client/server setup and that's outside of his main requirements.
bill
Tuesday, December 19, 2006
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz