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.

Is it a good idea to access a database directly from an app ?

I have built a timesheet application for my company (around 25 employees). I did it in 10 days using the VB Express Edition, and I had never ever seen VB code before that.

Currently, the desktop application directly accesses the database server (a MySql database), without any intermediate server handling requests as in the case of Web Based applications.

Is this the "proper" way to do things, and is the design scalable at all ? What other problems can arise in this design ?
Anindya Mozumdar Send private email
Saturday, May 20, 2006
For 25 employees it can be fine, if you have thought about security properly. How do you store the connection string. Is your app using one login to access the database or have you created a separate login for each employee. How about SQL injection vulnerability. Is the database server dedicated to your app or does it hold business critical data and is also used by other more important apps.
This is not the best design but may suffice it for your small company. I assume that both presentation and business logic is in your standalone app which has to deployed to each employees desktop. In real scalable system the buisness logic layer are ususally physically and logically separated. Think about a global company with offices in 50 countries with 50000 employess to track their timesheet. Of course the design should be 3(or n)-tier. But don't overengineer for a small company like yours.
Saturday, May 20, 2006
No, it is best to have multiple XML configuration files; some on the server and some on the client. You will want to use multiple frameworks to handle all of this for you with just a few more dozen XML files used by the frameworks to bring in your objects. Also, role a couple of your own custom frameworks to handle the areas that the other frameworks are not very good at. Make sure your custom frameworks are left undocumented. If you are any good, you can make all of the frameworks work harmoniously together and you won't have to do any actual coding anymore and just have to edit a few hundred XML files to keep your program running.

This will add simplicity to your direct approach and make life a whole lot easier for you and others down the road.
Saturday, May 20, 2006
Saturday, May 20, 2006
I could not agree more with CIFCChamp...
Saturday, May 20, 2006
Sounds like you solved the problem at hand nicely using a good approach for this situation. You also learned a new skill to boot. Good job.

It probably doesn't scale well for the reasons mentioned above. However it doesn't need to scale today the way you describe it. If in the future the requirements change regarding the number of users then perhaps your approach should be revisited. But that is a future potential issue you don't need to spend time or money on today.
cbmc64 Send private email
Saturday, May 20, 2006
You comment is thoroughly appreciated - funniest thing I have seen on the internet all day today.
CJohnson Send private email
Saturday, May 20, 2006
Going after the database directly is fine but you will want to do so by putting all database access into its own application layer. The opposite approach is to string database code all over the place which makes it harder to switch out later. If you put all database access into its own layer then you can switch it out in the future and replace it with web service calls to a centralized server. This would potentially be more secure and more accessible. But it doesn't sound like you really need to do that now.

The biggest issue I've seen with direct database access is that it forces a particular network topology. Since you are making direct connections to the database you can't be sitting on the other side of a firewall or even on another subnet without special configuration. This is fine if everyone is local but as soon as someone wants to connect from home you are screwed. And from a security standpoint you don't want to open up direct access to your database over the Internet. This basically means that you are only able to handle local users with your current configuration.
Saturday, May 20, 2006
One issue with this architecture is that it can make tuning the SQL rather challenging ... to change a single SQL statement you have to redeploy the client application.
David Aldridge Send private email
Monday, May 22, 2006
CIFChamp - Please do not further harm these forums.  Your reply is not helpful to the poster and therefore inappropriate.
Monday, May 22, 2006
Ah Common! That is the j2ee way and I am just pointing out that it is very easy to over engineer a solution. For those that work on applications like that, it is frustrating. “CJohnson” knows what I am talking about.

Anyway, I will try and not cause further “harm” to the forum.
Monday, May 22, 2006
Don't worry CIFChamp. The rest of us understood your humor. I guess some people are walking around with their panties in a bunch today.
Monday, May 22, 2006
Thanks for all your responses - direct or sarcastic.

1. I do have a separate database layer which exposes a set of APIs, and it should be relatively simple to switch these with calls to a centralized server later.

2. The network topology is a more serious problem. While it is of not of immediate concern, that will hinder adoption of my tool as the company grows and all the efforts will be wasted.

3. I was only considering the possibility of using some framework like RoR or Zope, just for the sake of learning it - as my primary job is not of a programmer - and I do it just for interest of learning new things.

Thanks again.
Anindya Mozumdar Send private email
Tuesday, May 23, 2006
Note to all: The "Joel" above is not Joel Spolsky: there's not a green checkie by his name.
example Send private email
Tuesday, May 23, 2006
I hope you took care of CONCURRENCY?

What you have here is a typical “Fat client” and there is no real problem with scalability.  The problem is deployment, every time you make a small change, you need to re-deploy to everyone. If it’s just 25 people sitting in the same location, it’s not a big deal.

If you separate your presentation logic from your business logic like you did with the data access, a later transition to a thin client should be simple (if you believe in fairies).
Marius Mans Send private email
Tuesday, May 23, 2006
You will also want to look at licensing costs. Having 25 users connecting directly to a database usually means that you need 25 CAL's. On the other hand, having 25 users connect to a web server that in turn connects to a centralized database through a small connection pool only needs as many licenses as there are concurrent connections. And even then you can often get by on purchasing a single "seat license" instead of trying to figure out the maximum number of connections required.

Just something to be aware of.
Turtle Rustler
Wednesday, May 24, 2006
"The problem is deployment, every time you make a small change, you need to re-deploy to everyone. "

That's not hard to solve either.  You can park the executable on a share and have the users launch from the same executable rather than putting copies on workstations.  We've done this successfully for years with 65+ user base in telemarketing field; logging phone calls, sales order entries, various reports and commission processing...

You could also have auto-updates in your app. but this fragments the thread more and I won't say any more other than it's not as hard as most would make it seem.

I don't see any problems with accessing the DB directly from the app.  Our enterprise/consultingware product does this today.  It's written in C++ and a thin DB abstraction class was written so that we could talk to, mainly, a postgresql database system which, incidentally, can be a pool of DB servers if our clients need to scale to more backend servers.

The problem with this approach, that we found, is when you want to move parts of your app. to a web facing app.  It's difficult to do -- and you wind up with a mish/mash (technical term for a messy or disconnected logic) of code in CGI and fatclient.  Fortunately for us, PostgreSQL has EXCELLENT stored procedure support in many languages. :)

Tools like Citrix and Terminal Services can help here but they add to the cost of your solution and may prevent smaller companies from using your stuff.

My 2 cents...
Thursday, May 25, 2006
"...a small connection pool only needs as many licenses as there are concurrent connections. " Turtle Rustler, this is a common assumption that is quite wrong.
Duncan Smart
Wednesday, May 31, 2006

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

Other recent topics Other recent topics
Powered by FogBugz