The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Designing multi-company databases

I'm designing a website that can be used by multiple companies.  It seems that there are two options:

1) The set of tables used by a company is owned by that company.  This implies a database per company.  This allows each company to upgrade individually but could cause managment problems with having many databases.

2) Use only one database but place a company ID column on each table.  This implies only one database for the entire website but restricts companies from being able to upgrade independantly.

Are there any other options besides the two listed above?  If not then which option do you think would be the best?
amazed
Monday, October 09, 2006
 
 
Also it seems that with having a database per company restricts me from using most hosting providers since they only allow one or a few databases to be created.  And I would have to go with a more expensive dedicated solution.  At the moment I'm trying to strike a balance between keeping costs down while also making it maintainable and expandable for the future.
amazed
Monday, October 09, 2006
 
 
Postgres, and perhaps others (though not MySQL), have the concept of 'schemas' that allow you to effectively have multiple, independent 'namespaces' within a single database.

http://sql-info.de/postgresql/schemas.html has a reasonable explanation.

If it's being designed from the start for multiple companies, is restricting yourself to what shared hosting can provide really necessary?
G Jones
Monday, October 09, 2006
 
 
Keep it simple. Use a single database, if requirements change later on you can always export the company identified data into a seperate database.
cough
Monday, October 09, 2006
 
 
Without knowing your business requirements it's impossible to know which is better.

But, the reason that you are choosing (to save on hosting) seems a stupid reason.  Even crappy cheap hosting accounts usually allow something like 10 MySQL Databases.  And decent cheap hosting accounts often allow more than 10.  And even those hosting accounts which are penny-pinching bastards, usually charge $1/month or less per extra database.

If you can't afford a database per business, then you've either chosen an awful hosting company, or your margins per customer are so slow that you'll never make any profit anyway, or both.
Sunil Tanna
Monday, October 09, 2006
 
 
You state the problem as "*can* be used by multiple companies" - so I am going to be picky and say "don't design for the future - too much".

ie not "we have multiple customers and this system *needs* to handle multiple companies, tomorrow".

ie you currently have no customers (it appears from your post), so develop the database and the application for one company, keeping in mind the possibility that you may have to re-eingeer it later for multiple companies. Make some attempt to keep the design as loose and flexible as possible, without expending too much energy on things you have no need for today.

If you gain another customer, you can copy & paste the database and codebase and you're good to go. Have their payments fund a rewrite or modification cycle that implements the multi-company design if you want to.

Long-term, I'd personally go with the multi-company set up / design. I have done it in the past and with (eg CompanyID : Integer) being so prevalent in tables / code you can almost hide its entire implementation at some superclass level and not really ever have to deal with it first hand. And that was using classic ASP.

HTH
Aaron
Aaron DC Send private email
Monday, October 09, 2006
 
 
I would definately have one db per company.

The reason is, if you have low traffic now, the server they are all on will be ok, once traffic increases, you will want to move them onto seperate boxes.
D in PHX Send private email
Monday, October 09, 2006
 
 
Also,

There is a definate risk to keeping all the data from multiple companies in the same db.

Depending on your contract & laws, they probably own that data.

What happens if an update runs and copies data from one company to another?

Not a fun position, and one that could be potentially be a big legal problem.
D in PHX Send private email
Monday, October 09, 2006
 
 
We have this feature in our Accounting/ERP software.  We most definitly used a seperate database for each companies for a multitude of reasons:

1.  Some of our customrs like to go back in time to look at purged information.  In PostgreSQL, we keep nightly snap-shots online in a compressed tar file.  To load one of those images, takes less than 10 minutes and we use a seperate database to handle this.  In our software it looks like another company.

2.  We implement a customization only for a particular company and wouldn't want the schema definitions in with other company databases and this change sometimes does not make sense to have it's own schema (name space).  One of my customers uses our software to run two companies and he can switch between the two by choosing his company name during logon.  One of the companies has slightly different schema definitions.

3.  One company gets more traffic than the other, then it's a simple matter of moving one of the databases to dedicated hardware without the fuss of data migration which could amount to a lot leg work (sql exports & loads) as well as putting a load on your servers to handle such exports.

4.  Schemas (name spaces) don't lend themselves well to company seperation in our case.  In our case, we have seperate schemas in our database to sepearte accounting tables from "application" tables.  The application tables store report logic, what menus users can see, screens, and what not.  Many of our screens are described in an XML format we store in the database in our application schema.  Had we used a schema for each company, we would then be forced to create ugly schemas for each application schema like company1.application, company2.application as oppose to just "application" within the companyX database.

5.  Table spaces.  If company1 has a particular table that gets hit harder than in company2, using seperate databases allows you to create seperate table space entries for each.  Otherwise a performance hit from company1 could affect company2.  Admittedly this is rare but a potential concern of ours in some situations where one company does "batch" processing and the other does not.
~Eric
Monday, October 09, 2006
 
 
We've built several multi-company/party apps. A database per company has worked out better and simpler for everything(we've tried it both ways).

When you keep the schema's under control and have only one version of your source code for all instances of your app, we've found that it works out much simpler and easier to manage.

In a high volume situation(lots of new customers) you might want an automated solution, but if you already have to handhold each sale, the extra work to load the build script and setup the instance will be the least of your problems.
Matt Estes Send private email
Monday, October 09, 2006
 
 
Been there. Done that.

Do separate databases per company. I could type some real long essay about what happened and why and what could be done to prevent it. Or you could spend a lot of time and grief rediscovering just how disasterous some designs can be. And the short answer is: separate databases per company.
Peter
Monday, October 09, 2006
 
 
Thanks to everyone for their inpu!  It definately looks like the majority is for having a database per company instead of sharing a single database.  That probably is better and more manageable in the long run.
amazed
Monday, October 09, 2006
 
 
what about a hybrid approach...

Identify all the tables having limited number of rows (master tables) and keep them in a single database with CustomerId in every table...

Keep all the other heavily transactional tables into a separate database per company...

this will save you the management cost for common tables and will also give you the advantages of "single database per company" approach as mentioned by every one else...

BUT!!! there will be a performance penalty because of  cross-database queries...
kid Send private email
Tuesday, October 10, 2006
 
 
It really depends on what kind of application you're building.

If you replace the word "company" with "user" then I think we'd all agree that it's usually a bad idea to have a separate table for every user.

For example, if you're developing an application to track the billable hours and expenses of company employees, and you want to offer this service to thousands of different companis, as a hosted solution, I think it would be a bad idea to have a separate database for every customer. Do you really want your application simultaneously connecting to thousands of different databases (and, therefore, thousands of tables)?

And, no I also don't think it would be a good idea to have a CompanyID column on every single table. You should have a 'companies' table, where you give each company a unique identifier. Then, in your 'users' table, each user has both a 'company_id' and a 'user_id'.

Your 'org_chart' table wouldn't need to have a company identifier, because each employee in the org chart would already have a company identifier.
BenjiSmith Send private email
Tuesday, October 10, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz