Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am writing an system for the company to put their promotion material on the site, which is like some kinds of CMS. So, I need a database that store their data, but I am considering how to store their data.... First approach is use a single table to store their data, but use a column, like 'company_code' to distinguish the company, another approach is create a separate database for a company....

(Of course, will check the company_code or company table name is existed or not, if yes, will assign a new company_code or company table name by programme.)

So, which approach is better? Thanks.....

share|improve this question
1  
Define "better". Using a separate database is "better" if -- by better -- you mean "uses as much DBA time as possible" –  S.Lott Feb 14 '12 at 16:39
    
Please clarify your post: Does the CMS in question store information about customers for you, in one system? Or is it a CMS that customers will purchase and use and host on their own? –  FrustratedWithFormsDesigner Feb 14 '12 at 18:09

2 Answers 2

I'm not sure there's enough info here to answer properly... Are you planning to store data for multiple companies in this system?

The way I would do it (based on the info herE) is give them their own database (I'm assuming here that your "database" has multiple tables per customer and that they could be hosted on the customer's server). If other customers want the same system, they would get their own database too. Changes/upgrades to the data structures would be carefully deployed to all customer databases via SQL scripts, after careful testing and pre-upgrade back-ups, of course!

On the other hand, if you're only storing a little bit of data for each customer for your system to make use of, then it might make sense to store all companies in a single table.

You might have to describe this problem a little bit better for a clearer answer.

share|improve this answer

Having a database per company isn't really a scalable or maintainable solution. Unless you know that there will only be a couple companies involved, I'd recommend avoiding it.

Relating a Company (via company_code) to each table containing company-specific data is a reliable solution that has worked for me numerous times in the past. It's relational, maintainable, scalable, and it makes it simpler to do cross-company queries for metrics and the like. It's the way to go.

share|improve this answer
    
Having a database per customer is normal when a packaged product is being sold to customers and customers are hosted separately. If they are storing info ABOUT customers for their own system to use, then yes, a database per customer is not a good idea. The OP needs to clarify which they're doing. –  FrustratedWithFormsDesigner Feb 14 '12 at 17:12
    
Agreed. If scalability is a non-issue, then db/customer is fine. –  Matthew Flynn Feb 14 '12 at 17:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.