Need help in SQL server and C#.net problem.

Dear all,

I have a situation like:

While registering new company in ERP software. It will create 2 new databases one for company details another one is for corresponding all the table in another database. For example I have registered a Company called Company1:

Lets check after registering this company what is happening in the SQL server.

Company details like companyid, companyname, databaseid etc will be stored in one database: And all the transaction details tables will be stored in another database, among all table GLAccount is one of the table.

Lets describe: step wise:

Step1:

After registering created database say for Database1... in this database company table say for companydetails: Fields are given below

Companid companyname datbaseid

Note: while registering any new company will be stored in the above table.

Step2: After registering company all the installation table will be stored in another database (it will create new database with company name itself) In this case second database will be Company1: (because companyname registered as company1)

In This database One table is GLAccountDetails: in this fields are

accountname,accountindex, accountnumber.

In this table there is no field like databaseid(so that we can link this table to another database table i.e company details).

My requirement is in the Dropdownlist I want to populate all the company from database1(from step1) and based on company selection I want to populate all the GL accoutnumber in another dropdownlist. How can I link these different database so that I can get the correct GLAccount details. Please help me to achieve this functionality Your help is always appreciated.

Thanks in advance.

As you mentioned there's no link between DatabaseId and GLAccountDetails table then either create a new field in AccountTable or in Comapnydetails table. check for the cardinality of relationship if it 1-1 or 1- many or many-many.
for 1-1 directly save the account details in company detail table by creating new fields in it.
if 1- many then use primary and foreign key concept between these tables.
i don't think if many - many relationship should exist in account and company tables.

so you can go with approch #2. Alternatively if you don't want to create new fields in these tables then create a 3rd table i.e. relationship table and save the primary keys of Account and company relatively. But it'll slightly increase the cost of maintaining a 3rd table.

once relationship create you can query the tables based on your dropdown selection to show the related GL accounts.

hope this make sense to you.
Thanks.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Choudhary15y ago
As you mentioned there's no link between DatabaseId and GLAccountDetails table then either create a new field in AccountTable or in Comapnydetails table. check for the cardinality of relationship if it 1-1 or 1- many or many-many.
for 1-1 directly save the account details in company detail table by creating new fields in it.
if 1- many then use primary and foreign key concept between these tables.
i don't think if many - many relationship should exist in account and company tables.

so you can go with approch #2. Alternatively if you don't want to create new fields in these tables then create a 3rd table i.e. relationship table and save the primary keys of Account and company relatively. But it'll slightly increase the cost of maintaining a 3rd table.

once relationship create you can query the tables based on your dropdown selection to show the related GL accounts.

hope this make sense to you.
Thanks.