Re: DB design for a multible company accounting system
Posted by: Sebastian Mayer
Date: December 18, 2014 08:44AM

>> max table has 4 M rows (but only 3 tables are so big)

> Is that a limit you are imposing? Or saying that 4M is the largest encountered so far? Every 'company' has three 4M-row tables?

It is by far the bigest. The average company has 2000 rows / year, 4 years are available i. 8000 rows

>> no query will touch 2 different companies.

>Your API should ensure that. Do NOT let users write queries.

Sure

> What sensitive data are you storing? If any, then be sure to do a thorough security audit.

Ok

> How many queries per second do you anticipate? If the average company does a dozen queries per minute, you could be swamped.

There are max. 20 clients working simultaneous on the db i. e. max. 20 differnt companies may be touched and cause its a dialog system no more then 20 queries per second

>> companyId as part of key

> Will _any_ queries look at multiple companies?

NO

>If companyId is part of virtually all keys, the be sure it is as small as practical, such as SMALLINT UNSIGNED. Then have another table that has info about the companies -- about 2000 rows long, with companyId as its PK.
> companyId should probably be the first part of each index.
>I agree with Peter that that is likely to be the 'right' approach.

> What is the total data size? 3*2000*4M rows? That's about 2TB ! Maybe I misunderstood you. If it is over 100GB, let's talk further about performance issues that you _will_ encounter. Please provide SHOW CREATE TABLE, etc.


At present time we are using a Cobol environment with ISAM Files. Every company has its own folder. The total size of all company data stored in Cobol isam files is 300 GB. I think that converting it to SQL will not change relevantly this size


Coming from cobol isam files, stored in different folders, I am wodering if it would be possible to do the following:
As I said no more then 20 clients are working simultanoues on max. 20 companies. 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"?

Options: ReplyQuote


Subject
Written By
Posted
Re: DB design for a multible company accounting system
December 18, 2014 08:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.