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.
Subject
Written By
Posted
December 16, 2014 02:19AM
December 16, 2014 12:00PM
Re: DB design for a multible company accounting system
December 17, 2014 06:54PM
December 18, 2014 08:44AM
December 18, 2014 04:42PM
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.