Re: DB design for a multible company accounting system

[Rick James]

> I think that converting it to SQL will not change relevantly this size

COBOL is rooted in punched cards. The direct translation would involve fields like
name CHAR(50)
But it is better in MySQL to do
name VARCHAR(50)
That will soften the size requirements some. (Still, "300GB" is a handy to know.)

> So when client 5 starts working with company 1252 (which he will do for several hours) would it be possible to assign table "openItem05" to "folder1252/openItem"?

MySQL is quite happy to have multiple clients simultaneously working on their company info. Are you suggesting that only one client should be working on a given "company" at one time -- that is blocking other clients "for several hours"?

If you need such blocking, let's talk further. InnoDB "transactions" is not the way to achieve that.

max_connections = 20 is one way to limit things to 20. But do you really need such a limit?

> 300GB -- 2000 companies -- 20 at a time

That adds up to only 3GB of data being touched at a time. So, even a modest-sized machine should be able to cache most of the data most of the time. I don't see any performance issues -- unless the "several hours" includes big number-crunching reports. If so, please elaborate so we can look at potential issues there.

> isam files, stored in different folders

MySQL stores data in its own directores/files (for any of the designs); they are not readily accessible to you. If there is any need to retain the "folder" concept, say so; we may have a workaround.