Thread: 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 -- > > 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