Re: DB design for a multible company accounting system
Posted by: Rick James
Date: December 17, 2014 06:54PM

> 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?

> no query will touch 2 different companies.

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

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

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

> companyId as part of key

Will _any_ queries look at multiple companies?
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.

Options: ReplyQuote


Subject
Written By
Posted
Re: DB design for a multible company accounting system
December 17, 2014 06:54PM


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.