multi-company design/structure ?
Hi,
Is it better to create multi databases or create multi schemas?
I am writing a program that can have multi instances. Sort like a finanical
accounting system that can have multiable companies. Where each company has a
different name but the tables are an exact match to each other. IOW the only
difference between the company tables is the data that each instance
contains.
I believe that if I use multi-schemas within one database it will be easier to
retrieve data from other schemas. But what if the data has to be on
different computers (or multi locations) - is it true I have to insure all
the schemas are in the same data location? Or can the schemas be on
different computers.
Anyway I'd like to hear from the list opinion as to the best way to design
this structure.
Also I believe the database/s will be run on Linux.
Thanks in advance,
Johnf
Is it better to create multi databases or create multi schemas?
You're missing one option imho: One database, one schema.
I am writing a program that can have multi instances. Sort like a finanical
accounting system that can have multiable companies. Where each company has a
different name but the tables are an exact match to each other.
Then you could simply add a "company-id" column to every table (and make this column part of the primary key of each table). And if you run multiple instances of the program within _one_ company, then you can also add an "instance-id" column (and make this column also part of the primary key of each table).
Sincerely,
Wolfgang
--
NO "Courtesy Copies" PLEASE!
On Tuesday 20 October 2009 10:11:53 am Wolfgang Keller wrote:
Is it better to create multi databases or create multi schemas?
You're missing one option imho: One database, one schema.
I am writing a program that can have multi instances. Sort like a
finanical accounting system that can have multiable companies. Where each
company has a different name but the tables are an exact match to each
other.Then you could simply add a "company-id" column to every table (and make
this column part of the primary key of each table). And if you run multiple
instances of the program within _one_ company, then you can also add an
"instance-id" column (and make this column also part of the primary key of
each table).Sincerely,
Wolfgang
I never even considered using the one database with added company
field/column. On the surface is sounds OK but I'm not to sure. Each SQL
statement would require company_id. Hmmmm.
Johnf
On Tue, 20 Oct 2009, John wrote:
I never even considered using the one database with added company
field/column. On the surface is sounds OK but I'm not to sure. Each SQL
statement would require company_id. Hmmmm.
Johnf,
Why not take a look at the sql-ledger code? It's a series of perl scripts
and open source. Perhaps that will give you some ideas because SL can be
multi-company, multi-user, multi-currency, etc.
Rich
On Tuesday 20 October 2009 11:59:54 am Rich Shepard wrote:
On Tue, 20 Oct 2009, John wrote:
I never even considered using the one database with added company
field/column. On the surface is sounds OK but I'm not to sure. Each SQL
statement would require company_id. Hmmmm.Johnf,
Why not take a look at the sql-ledger code? It's a series of perl
scripts and open source. Perhaps that will give you some ideas because SL
can be multi-company, multi-user, multi-currency, etc.Rich
thanks
Johnf
John wrote:
Hi,
Is it better to create multi databases or create multi schemas?
John, I just gave a talk on multi-tenant Pg clusters at PgConf West
2009 that may help you but ran into vehicle problems and just got home
this evening. As quick as I can I'll get the bits-and-pieces pulled
together and pass them along.
Too frazzled to give it a try this evening. :-(
\\||/
Rod
--
Show quoted text
I am writing a program that can have multi instances. Sort like a finanical
accounting system that can have multiable companies. Where each company has a
different name but the tables are an exact match to each other. IOW the only
difference between the company tables is the data that each instance
contains.I believe that if I use multi-schemas within one database it will be easier to
retrieve data from other schemas. But what if the data has to be on
different computers (or multi locations) - is it true I have to insure all
the schemas are in the same data location? Or can the schemas be on
different computers.Anyway I'd like to hear from the list opinion as to the best way to design
this structure.Also I believe the database/s will be run on Linux.
Thanks in advance,
Johnf
The problem is how you use those data ?
I have used schemas to split data when I had to manage large amount of
data (hundred of thousand records) that are (almost) never going to be
used together, if not for statistic purposes and offline processing.
If you never need to select those data all together (different companies
and instances access only their own data) and the amount is really large
than you can have benefit from splitting, otherwise adding a field is
going to be more efficient.
John ha scritto:
On Tuesday 20 October 2009 10:11:53 am Wolfgang Keller wrote:
Is it better to create multi databases or create multi schemas?
You're missing one option imho: One database, one schema.
I am writing a program that can have multi instances. Sort like a
finanical accounting system that can have multiable companies. Where each
company has a different name but the tables are an exact match to each
other.Then you could simply add a "company-id" column to every table (and make
this column part of the primary key of each table). And if you run multiple
instances of the program within _one_ company, then you can also add an
"instance-id" column (and make this column also part of the primary key of
each table).Sincerely,
Wolfgang
I never even considered using the one database with added company
field/column. On the surface is sounds OK but I'm not to sure. Each SQL
statement would require company_id. Hmmmm.Johnf
--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================
On Tuesday 20 October 2009 10:05:34 pm Roderick A. Anderson wrote:
John wrote:
Hi,
Is it better to create multi databases or create multi schemas?
John, I just gave a talk on multi-tenant Pg clusters at PgConf West
2009 that may help you but ran into vehicle problems and just got home
this evening. As quick as I can I'll get the bits-and-pieces pulled
together and pass them along.Too frazzled to give it a try this evening. :-(
Thanks, I'd be very interested in reading/seeing what you have available. I'm
still in the planning stage so I can wait a few days.
Johnf
On Wednesday 21 October 2009 01:23:18 am Ivano Luberti wrote:
The problem is how you use those data ?
I have used schemas to split data when I had to manage large amount of
data (hundred of thousand records) that are (almost) never going to be
used together, if not for statistic purposes and offline processing.If you never need to select those data all together (different companies
and instances access only their own data) and the amount is really large
than you can have benefit from splitting, otherwise adding a field is
going to be more efficient.
I don't see any one company having much more than 50 Gigs. So I'm not to
worried about the size. But I do see wanting to create aggreate type reports
of the companies (like a HQ wanting to report an aggreate GL report of the
companies). I would think both the using company_id column or using schemas
to separate the companies would allow easy access for the aggreate type
reports.
Food for thought,
Johnf