How to maintain separate database for company

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I deveploped windows application Using VB.net and SQL2005

I had tables in the database as

Company,Customer,Supplier,Purchase,Sales tables...

I need help for do the following work.....

When the user create one new company, separate database want to create at the name of company,,,, data of the particular company want to store in it's database.....

Give idea for this.........

Posted 28-Jul-12 19:20pm

PunithaSabareeswari

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2012-07-28T19:27:00

Solution 1

You have to create databases as needed, or create a whole new layer in your DB, so that one DB can hold data for many companies. Or do you mean how do you DISTRIBUTE a DB with your installer ? Using SQL Server lite edition makes the most sense, as it is free.

CREATE TABLE
COMPANY
begin
id int identity(1,1),
name nvarchar(200),
username nvarchar(200),
password nvarchar(200)
end

I'd have usernames in another table, so there could be many for one company, but, the point is, company.id is now added as an identifier to your current top level tables, and then any data below that, maps back through the middle table so it is also associated with a company. Now your app needs to require a DB login, which will mean any data it creates or uses, will only be visible to users from that company.

This is all assuming you have ONE DB server, that all these DIFFERENT companies will use.

Posted 28-Jul-12 19:27pm

Christian Graus

Updated 28-Jul-12 20:03pm

v2

Comments

PunithaSabareeswari 29-Jul-12 1:35am

hi... Thanks for ur idea.... but i want to some depth knowledge in this.. can u give...
i'm learner of database.... that's why i needed clear info for this... plz...

Christian Graus 29-Jul-12 1:38am

I think it's not clear what you want. You want each user of your app to set up their own copy of your database to connect to, and each DB will be on a different server/network, right ? So, it's really a deployment issue ? I would give people SQL Server Lite Edition ( or whatever they call it now, the free one ). Then I'd expose a way to set the connection string in the app, but give it a sensible default. Then I'd add code in the program that can build a new database, such as shipping the SQL needed to do it, so you can connect and create an empty DB if needed.

Is that the sort of thing you need ?

PunithaSabareeswari 29-Jul-12 1:47am

ya... correct man... all databases want to store in the server system of LAN network...

Christian Graus 29-Jul-12 1:50am

OK, so they will all be on the same server ? In that case, it's probably easier to build your system so that you have one higher level, of company names, then every other bit of data links back to the company id, so each company can only see their own data.

PunithaSabareeswari 29-Jul-12 1:55am

i think u got my needs.... when i want to do this all while creating company or else...

Christian Graus 29-Jul-12 1:56am

If you create a DB for every company, you can't easily compare data between them and you have a hugely manual process. If you design your db to have a top level username and password for each company and to store data for all companies, that will be more maintainable

PunithaSabareeswari 29-Jul-12 2:00am

Really.... Confused....................... Wat i want to do.............

Christian Graus 29-Jul-12 2:01am

I am editing my answer

PunithaSabareeswari 29-Jul-12 2:05am

ok... pa.... i'm waiting......

Christian Graus 29-Jul-12 2:06am

That's it.... You create a table that identifies a company, so you can store data for many companies, and each sees only it's own. I have to leave now, so I hope that helped.