reccommend the best database engine

[jack616]

I'm creating an application with a built in mySQL database and was wondering if anyone
had any advice on which engine (innoDB or MyIsaM) and which collation would be best
to use if the database was to have the widest ease of use for people?

The database will be fairly small but with linked tables but not updated very much.
It will be a low access system and speed isn't much of an issue.

Are there any real problems or considerations I'd need to consider?
Does any of this even matter in my case do you think?

thanks

 

[taazz]

if the data are not updated a lot and the database is in single user mode ee local or embedded then myISAM has the best speed and probably is the wisest choice if on the other hand you need data integrity then innoDB would be my choice. of course I'm against using mysql my self since there are other more complete sql databases out there with a more liberal license like postgres or firebird.

[Leledumbo]

Do you need referential integrity (big chance you will, unless your tables are not normalized to at least 3NF) and you want the engine to maintain it? Then InnoDB. Otherwise if you don't need one, or you are willing to maintain it by yourself, go for MyISAM.

[JJVillamor]

Instead of a new thread, I hope this won't be much of an OT but rather an expansion of the discussion, what if?

1) speed is preferable
2) about 100-150 records daily
3) suitable for someone not well versed in Sql aside from some basic SQL statements

[BigChimp]

@Jack: for my information does "linked tables" mean that MySQL can/will access tables in another database format? Or just the fact that you have set up referential integrity with joins etc?

Instead of mysql, why not use Firebird embedded - I prefer this one because it is full-featured, well supported by FPC and allows a very easy upgrade to the server version -  or sqlite? Mysql support in FPC/Lazarus means you have to upgrade your connector every time you use a new client library. The connector may not be available etc.

Also, I don't know about under what terms the application will be released, but MySQL licensing policy is rather strict...

[cpalx]

postgresql is the best.

[exdatis]

I have a very good experience with embedded Firebird, super-srv Firebird.
Regards
p.s. I have experience with Firebird 2.0 - 2.5 and PostgreSQL 8 - 9.2(excellent db)

[poiuyt555]

I have some very good expirience with 1c:enterprise 8.
In my oppinion - create 2-10x faster your apps, rather than as usual.
I was working with Visual foxpro earlier.
But 1c - have some dependence in distribution of the 1c firm (get the info from 1c firm directly) and the source code of your app may be opened for the future external development (if i correct understand).
But create your app - very easy: you dont need to write your own protection system, work in network environment, users, ets... - it is all already done.
You just simply set your documents, directoryies and reports...
And work...

Also it has some build-in administrative tools: save/load your config and databese to an archive, automatic testing anf fixing, adding users, displaying active users, event log, external universal units ex. save and load any data between the equivalent configurations and ets...

in my opinion, it provides the creating of your apps with the minimum of source code.

1) speed is preferable

- 1c has native file server engine, and in my opinion, it works completely fast.
(also as written it may works (from web):in a three-tier architecture with a separated application server (1C:Enterprise and MS SQL Server, PostgreSQL or IBM DB2 databases)

2) about 100-150 records daily

i think this is not a problem for 1c.

3) suitable for someone not well versed in Sql aside from some basic SQL statements

1c has (form web) native SQL-like query language, supporting all SQL standard, but extending it with more business tasks-oriented features.

but when i was looking for myself for completely independent solution i choose Lazarus+Firebird, but at this moment i do not check this variant and do not work with them.

[BigChimp]

@jack616: what do you think of our responses?

[jack616]

My thanks to all who replied:

Hi BC - Sorry I just typed a comprehensive answer here and the forum timed out before I could post it and then lost it. Perhaps the forum could be adjusted to keep posts that happens to somehow.

MySQL is the only database package I would consider for this project.
Essentially your assumptions are correct. But no other formats except I expect I'll include
an import/export for CSV. There is no specific networked requirement.

I'm not totally convinced I even need to normalise this to get the best out of it.
That being said I'm starting out that way because the records are large and complex.

The database will be FOC and no licence except a standard use at your own risk disclaimer.
As for MySQL licensing - I have no problem with the current status.
And client connectors - If FPC doesn't have them then I'll just have to stop using FPC.

I don't have time to repeat my info right now (I have to cook tea!!!)  but that seems to be the gist I think.
I've never distributed a database for widespread use and thought it worth asking.

If anyone spots or knows of a specific issue feel free to pipe in.

[jack616]

PS.. sorry..

comments on collation are welcome - I'm not at all experienced with that issue...
TIA

jack

[taazz]

1) no one asks for the license you are going to share the database or your application.
2) MySQL license does not allow to distribute a MySQL server or embedded engine in any kind of commercial application you need to buy a license to do that. You are not allowed to have mySQL's installation on the CD you distribute to your clients they have to downloaded and install it by them self.
3) I have never considered MySQL as a proper Database and although the features messing are not vital for web applications I would never dream to use it on any application that requires data integrity INNODB tables are closer to my liking but the last time I checked (around the 4.0 era) MySQL was nowhere near to my standards so I have moved on to other databases.

Hearing you say that you will stop using lazarus if there is no MySQL support makes me ..... well to put it simple if you where my client you would get charged twice the salary from other clients and in advance.

[silvestre]

Many Linux distributions are migrating from MySQL to MariaDB, compatible with the first. Would it be interesting to consider offering support in Lazarus? Note: http://sourceforge.net/projects/zeoslib/ available for Lazarus think supports MariaDB

http://www.infoworld.com/t/open-source-software/wikipedia-dumps-mysql-hooks-mariadb-217820?source=footer

[Leledumbo]

Many Linux distributions are migrating from MySQL to MariaDB, compatible with the first. Would it be interesting to consider offering support in Lazarus?

Isn't it compatible even at API level? I mean, we don't really have to do anything to connect to MariaDB, simply treating it as MySQL. CMIIW.

[silvestre]

Hi Leledumbo, I have no direct experience with MariaDB , maybe the following article will serve to clarify this issue.

https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/