Which Database?

[ross246]

Hi guys,

I have a small dilemna on my hands. I have created a time and attendance program that uses biometrics to clock people in and out. I currently am using a postgresql database but I am now working on deployment and starting to wonder if this was the best decision. The software might be deployed onto clients desktop machines and I don't know if it is overkill to distribute it with postgresql. I am very interested in sql server compact because it seems perfect in terms of deployment.

The databse is not too big I dont think?, it has about 9 tables, one table stores BLOB/images and the rest stores text really. In a year it could accumulate about 1.5 million new records in one of the tables (due to each time someone clocks in / out).

The only disadvantage that I can see is that my software has the ability to work over a network, so If someone wants two scan points in one location, they both point to one central postgresql database. If i migrate to a sql server compact database I wouldn't be able to access the database over the network? Unless I share the folder containing the database?

Basically just looking for some advice on what I should do

-Deploy application with postgresql database
-Migrate to sql server compact for an easy resource free desployment
-Another option I have missed?

Thanks in advance for the replies
Ross.

 

[Robert_SF]

Hi Ross, indeed you can have multiple users accessing a remote SQL Compact Edition database. Here's what the SSCE documentation says.

Multiuser support
To support multiple applications that access one database at the same time, SQL Server Compact 3.5 provides row-level locking of data pages, page-level locking, and isolation levels to help guarantee data integrity during concurrency. For more information about locking, see Locking (SQL Server Compact) and Lock Escalation.

CE does not support stored procedures nor multiple statements per query, however.

Beyond that, I don't know enough to advise you.