Suggest for the DBMS / RDD

[sudip]

Hello Friends,

I am now mainly working with SQLite. As of now my 3 small apps (with HMG) use SQLite. I like to start a new app for small shops (retailer shop, not wholesale). Can you please suggest me what DBMS / RDD should I use - DBFCDX, SQLite or big DBMS like PGSql or MySql.

I am considering DBFCDX, due to record locking facility, speed (at least for single user), ease of software development. But, chances of data corruption is also higher.

It will be great helpful if all of you please share your views regarding this subject.

Thanks in advance

[Rathinagiri]

I prefer SQLite for single user/small network solutions.

Recently, I used SQLite for an inventory project with 16 tables, complete foreign key constraints.

IMHO, SQLite is so far so good, since it is easy to install (nothing to be installed ), easy to maintain (nothing to be maintained ) and easy to backup (just copy the db file daily in a secure place).

In case of a server->clients architecture, where MySQL of PGSQL can be installed, maintained and backed up regularly, where a large number of users (more than 10) are going to use the database frequently or simultaneously, we can use MySQL/PGSQL.

[swapan]

Hello Friends,

I am now mainly working with SQLite. As of now my 3 small apps (with HMG) use SQLite. I like to start a new app for small shops (retailer shop, not wholesale). Can you please suggest me what DBMS / RDD should I use - DBFCDX, SQLite or big DBMS like PGSql or MySql.

I am considering DBFCDX, due to record locking facility, speed (at least for single user), ease of software development. But, chances of data corruption is also higher.

It will be great helpful if all of you please share your views regarding this subject.

Thanks in advance

Dear Sudip:

Please design your software with the kind of store-size you'll be catering to. I think your these stores will be doing with 1 computer. But you consider the following setup as minimum.
1)Billing Counters - 02 (the POS terminal)
2)Back office job - 01 (for purchase, accounts entry & maintenance)
3)Management/owner - 01 (for MIS & daily activity reports)

So 1 computer will work as Server and the other ones will work as Clients. Make the system SERVER as per the situation. POS terminal can also be made server if there's minimum requirement of the back office computer. And your this server will contain the database.

Please always consider in your design - minimum of two billing counters. Any stores with heavy footfalls or stores spread in floors or in different departments will call for atleast two POS terminals. And you may handle the billing/invoice nos. user/pos terminal-wise to cope with concurrency issue.

Now coming to Database -
Don't make any haste decision and let our fourm members who develop & maintain real life applications using HMG come up with their feedbacks. Your this design will be the backbone of your application......otherwise you may have to compromise a lot in future or re-write again.

I'm very much comfortable with DBMS but have to see its drawbacks also....moreover a few of your new clients will ask for which kind of database.....DBF files will not fetch good impression...eventually money.
What bout your MySQL or MSDE?! A little bit of today's pain may ultimately give you good gain in future. Try to shed your comfortability with DBFCDX environment and think rationally..... if we can still do with DBFCDX then fine, otherwise have to take a call.

Is there anyway way - start with DBFCDX environment and later on can switch to any other RDBMS with minimum level of code change. Main changes will be in routines to fetch data from the database and replacing data to database.

Lets discuss and come to a solution which fits your (and mine too, atleast!) bill.

[swapan]

I prefer SQLite for single user/small network solutions.

Recently, I used SQLite for an inventory project with 16 tables, complete foreign key constraints.

IMHO, SQLite is so far so good, since it is easy to install (nothing to be installed ), easy to maintain (nothing to be maintained ) and easy to backup (just copy the db file daily in a secure place).

In case of a server->clients architecture, where MySQL of PGSQL can be installed, maintained and backed up regularly, where a large number of users (more than 10) are going to use the database frequently or simultaneously, we can use MySQL/PGSQL.

Thanks Rathi for the quick feedback.........
Sudip, why you are not considering SQLite, what are the drawbacks? Hope its capable of handling multi-user environment. Its something like MS-ACCESS - I suppose. From Sudip I came to know bout this database, though not have tested with my hmg codes....it looked good as there is no "naked" data files like dbfs. Does it contains any password?

[sudip]

Rathi, Swapan,

Thank you very much for your posts

With SQLite, I have no problem Only, record level locking, date field etc. are not present. Just now I learned from Rathi, SQLite can handle Foreign Keys So, it's very good choice For larger database, may be PGSql will be a good choice (if MySql doesn't have licensing problem, may be it will be my best choice).

I don't go for Microsoft database as IMHO, MS Database technology works fine with MS Development tool.

For, real life POS and back-end system, I want to get some views from Marek

Thanks in advance

[Kana]

Firebird?

[sudip]

Hello Kana,

Thanks for your suggestion. I am not sure whether we can use Firebird with HMG? Have you used Firebird with HMG? If yes, please share your experience.

[karweru]

Hello Friends,

I've seen examples of how to connect/read/write to a *.mdb file. Anyone with an example of how to create the file in the first place? I read that sqlite is not ideal for a large multiple user environment: How does Mdb measure up? I would really appreciate any insights.

[sudip]

Hello Gilbert,

Thank you for your input

SQLite is meant for small database apps or it can be used as a test dbms for a larger multi-user system.

.Mdb is a very good choice. I tried it about 1 year back. But, I am not sure whether we can use ADORDD with HMG

I have some query regarding .mdb:

1. I want to create the database on the fly (ie., my app will create the database automatically.). During creation, it wants to check whether a table already exists or not. Is it possible with .mdb (without using VBA codes)?
2. Is it possible to use transaction like SQLite with mdb using BEGIN TRANSACTION .... COMMIT?
3. How is the performance of .mdb in multiuser platform?
4. Is it easy to transfer .mdb backend to larger DBMS like PGSql, MySql etc? After transformation of backend server, front end app requires very small changes in case of SQLite. I am not sure about changes needed for .mdb.
5. Does .mdb run on other OS, eg., Linux? (We hope HMG will run on other OS near future with GTK)
6. Will Microsoft support .Mdb in future? Currently, default database for VB.Net is SQL Server Express (please correct me if I am wrong).IMHO, Open source product is a better choice.

I shall tell about the real life performance of SQLite after 3/4 months (when my clients will have some data stored in their SQLite database ).