Link to home
Start Free TrialLog in
Avatar of cofactor
cofactor

asked on

Solution Design

I have an application which uses SQL Server 2008 database.
There is a table where I put user data of two countries i.e country 1 & country 2.
There are  references to other  tables so that  country 1 users can see country 2 records and vice versa through the application

Now , my requirement is  to segregate user data of country 1 into  DB1 and user data of country 2 into DB2 .
how do I manage existing  references now so that country 1 users still can see country 2 records and vice versa through the application although records  has been separated in two different databases ?

Thanks
Avatar of gurpsbassi
gurpsbassi
Flag of United Kingdom of Great Britain and Northern Ireland image

In memory joins?
or perhaps cross DB joins in SQL Server - which I don't have much experience of.
Avatar of Jim Horn
>how .. country 1 users still can see country 2 records and vice versa
Define 'see'.
You just have to change the queries to identify the databases the tables are in. For example:

Select   firstname, lastname, country
from (
select firstname, lastname, country from db1.dbo.usertable
UNION all
select firstname, lastname, country from db2.dbo.usertable
) a
order by lastname
Some of the answer depends on why you want to separate.

Right now (before separation) everything is in the same DB / same instance and has table reference to ascertain (query) access

If the current DB and machine is coping quite well with the demands, then, there isn't a structural problem per se. And makes it a lot easier, because it is a logic change, rather than driven by (physical) demand.

If however, the motivation to separate is due to demand on resources, then you will need to ascertain what the real demands are and how to resolve structurally. And that normally means money for a new server / disks / memory whatever.

It is often better to spend a (comparatively) little bit more on the current physical configuration if that will make the difference of country1 co-existing with country2.

So, the reason WHY the change will have an impact.
 
if you can create DB1 and DB2 on the same SQL instance / same server, then you can easily cross reference by including the DB name in your current queries where needed. You might be able to achieve that just by having different schemas rather than different databases.

An object in SQL Server (such as a table_name) is accessed by name. It is assumed that the object exists within the current context of where you are.

So, to access a table within the database you happen to be connected to, just use the table name. But if that table belongs to a different schema, then you have to include that schema name as well. Same goes for DB name and so on...

SQL Server uses a four part object reference in the form :
[server_name].[database_name].[schema_name].[object_name]

In sequence of increasing precision of accessing an object :
1) [object_name]  
          e.g. select * from MyTable
2) [schema_name].object_name
          e.g select * from dbo.MyTable
3) [database_name].[schema_name].[object_name]
          e.g select * from mydb.dbo.MyTable
4) [server_name].[database_name].[schema_name].[object_name]
          e.g select * from MySQLInstance.Mydb.dbo.MyTable

And they all quite legitimately refer to the same thing in the above example. If there is a difference at any of those levels, you need to clarify the level that the object belongs to.

The change would be changing the appropriate level to achieve the split into DB1 and DB2 and of course the queries used to access.

So, instead of something like :
select * 
from MyTable
inner join User_contol_table on <something>
where country_code = 'what I want'

Open in new window

It would become :
select * 
from DB1.dbo.MyTable
inner join DB1.dbo.User_contol_table on <something>
where country_code = 'what I want'

Open in new window

The challenge would be that shared table. Might even consider putting that as a third DB :
 
select DB1.* 
from DB1.dbo.MyTable as DB1
inner join DB3.dbo.User_contol_table on <something> as DB3
where DB3.country_code = 'what I want'

Open in new window

In the above, we also use Table Alias (a name given to the source identified by using "AS")

So then we get into the more intricate parts of the problem. Accessing isn't the real issue here. There is almost always a way. Flexible, yes, but it is a bit unforgiving.

It depends on the "WHY" and what you are hoping to achieve from the structural change.
Avatar of cofactor
cofactor

ASKER

>how .. country 1 users still can see country 2 records and vice versa
Define 'see'.

 country 1 user logs into the system , and   he can  view country 2 user records / reports / statements ,

 country 2 user logs into the system , and   he can  view country 1 user records / reports / statements ,

This  works because existing DB has references in these records .....so it becomes easy to pull up any vice versa records ....hope that explains.
@mrcoffee365


You just have to change the queries to identify the databases the tables are in. For example:

Select   firstname, lastname, country
from (
select firstname, lastname, country from db1.dbo.usertable
UNION all
select firstname, lastname, country from db2.dbo.usertable
) a
order by lastnam

This will work for views/reports . ...this is fine.....  (since two datbase will now remain in two different data center..so we can do  a DB link and go your way for sure)

But there is an issue here .....  Its regarding transaction ...see the case below..

currently when  country 1  user  opens a web form in application we  set evaluator of the form to a country 2 user.....Now if we separate the DB , how do I manage this reference ?


Also please note we have plan to add  more users to country 1  and country 2 in coming years
@Mark Wills

If however, the motivation to separate is due to demand on resources,

This is not about resources ...

we require to do this is because a   Data Privacy Law is coming in near  months  which requires  all Country 1 user records to stay in a separate DB  within Data Center  of  country 1 only  AND all Country 2 user records to stay in a separate DB  within Data Center  of  country 2 only.
@Mark Wills
if you can create DB1 and DB2 on the same SQL instance / same server, then you can easily cross reference by including the DB name in your current queries where needed.

No. we can not....... Its not permitted.

users are asking this ....

DB1 will be in Country 1 Data Center
DB2 will be in Country 2 Data Center

The challenge would be that shared table. Might even consider putting that as a third DB :
Yes. ..Please note user base will gradually grow on in these two database separately now in coming years. ..so they are going to have separate primary keys  ....... issue is ,  currently we have web form where we keep country 2 user record  as country 1  evaluator and vice versa....how do we manage these  references now if we separate Database  ?
Privacy laws coming into play reinforces the two DB's but can still exist on same machine. You can control / restrict access with security. It can be made as rigid as you want.

I would be inclined to restore a backup of your current into DB1 and DB2. You can then play with processes (backups and housekeeping) / views / data purge (of redundant data) / security / sql (and more) and develop sql scripts. By the sounds, you can keep same tables / naming conventions. Just that they are duplicate structures in separate DB's. Ideally the primary keys are surrogate keys rather than natural data - access via appropriate indexes for natural data. That might help with your concerns, though, I would suggest that (separate primary keys)  probably wont be a major concern in the final design because you will minimise the database structure changes, just their name and where they live at the DB level.

Those scripts will come into play when it is time to cut over. I would take the extra step of two new DB's because you can do so much testing and developing those scripts when it would become obvious (in a test scenario) if something breaks security, or missing data, or have access to data that shouldn't be permitted. The advantage is having the current environment to fall back on if things go pear shaped when cutting over.

Though, there could be the case to continue to use the current one for one of the countries and have the second as the brand new DB.

With the web form, you might consider the user DB as a separate DB (that DB3) to control access and keep attributes like which DB that user needs to access.

Ultimately, you should be picking up / seeding country attributes from the user because that will determine which database to log into.

So, that part will require some extra thought. I see what you have written, but the imagination isn't able to create the image / to paint the picture of your web form. And from my understanding, due to app / company sensitivities, you might benefit from some consulting services. There is a lot to consider.
@Mark Wills

With the web form, you might consider the user DB as a separate DB (that DB3) to control access and keep attributes like which DB that user needs to access.

I am not clear what you mean by this.

Existing system has a situation like below :
user1  from country 1 logs into the web application and fills a web form and select  user2 of country 2  as evaluator through a user search box.  as soon as user1 submits the form an email goes to user2 .  user2 now logs into system and opens the same web form and put comments and saves the form.


How do I manage this in New system ?  As you understand in New System I am planning to move user1 records of country 1 into DB1 and  user2 records of country 2 into DB2 .......... So What  I'll keep in DB3  so that they are still referenced   ?  Also when a new user is added in DB1 or in DB2 , how do I manage that in DB3  ?

You might give a small example explaining  how you are planning to fix this part.

Thanks
Referring to tables by using their database name works in all sql.  You thought you could only do that for Select statements?  Not true -- any time a table is referenced in sql, you can specify the database name.

It sounds as if you have never used multiple databases in an application.  The sql is straightforward -- use my example or the many you can find online.

Are you concerned about DBA issues like making a remote db available?  Check with your dba.  If you don't have one, any SQL Server dba book will walk you through the steps to make multiple remote databases available.  It's also fully documented online.

If your databases have to live on servers in different countries, then you might have performance problems in your application -- but that was the choice your company made to follow the requirement that some data live in the country where it originates (if I have your explanation right).

If you are looking for help on performance issues, please award points for the correct answer here, and start another question about performance and using remote databases.
Well, just to clarify that third DB scenario (which may not be needed)

It sounds like users are more of a global resource. So, don't add them to DB1 or DB2. Instead treat them as global and add them to a DB (ie DB3) that can be accessed independently.

That table (or tables) would have user attributes like email address, country and what ever else is needed, maybe role, "can be selected", and so on.

So when selecting a user from your web form, you access the one user table from that global area.

The other option is to add them to a table held within the realm of country (ie add users to either DB1 or DB2). Then make it "look" like the one user table by creating a VIEW of that table. Very similar to what mrcoffee365 said above (both his union query example and his post directly above)

Now a VIEW is not a table holding data, it is a reflection of that information and can span different tables etc. But you can then treat it much like a table in terms of a select statement. A stored select query is probably the easiest way to think of it :
-- tell SQL Server which DB to use

use EE_TEST_DB1;
go

-- create a table of users - we will add a superfluous 'country_1' just to clarify (ie table name should be country independant)

create table EE_country_1_Users (ID int identity, username varchar(40), useremail varchar(40), country varchar(10), selectable char(1))
go

-- now populate

insert EE_country_1_Users values ('Jack_1','jack@country1.com','country1','Y')
insert EE_country_1_Users values ('Jill_1','jill@country1.com','country1','Y')
insert EE_country_1_Users values ('Boss','boss@country1.com','country1','N')
go

-- now tell SQL Server to use the other DB

use EE_TEST_DB2;
go

-- create a table of users - we will add a superfluous 'country_2' just to clarify (ie table name should be country independant)

create table EE_country_2_Users (ID int identity, username varchar(40), useremail varchar(40), country varchar(10), selectable char(1))
go

-- now populate

insert EE_country_2_Users values ('Jack_2','jack@country2.com','country2','Y')
insert EE_country_2_Users values ('Jill_2','jill@country2.com','country2','Y')
insert EE_country_2_Users values ('The_Boss_2','boss@country2.com','country2','N')
go

-- create a VIEW that crosses over DB1 and DB2
-- a view gives access to data, doesnt store it.
-- easier to think of it as a stored query
-- and would exist in both DB (as meta data)

Create VIEW vw_EE_Users as
select username, useremail,country,selectable, 'COUNTRY 1' as origins
from EE_TEST_DB1.dbo.EE_country_1_Users            -- spell out [database_name].[schema_name].[table_name]
where selectable = 'Y'
UNION
select username, useremail,country,selectable, 'COUNTRY 2'
from EE_TEST_DB2.dbo.EE_country_2_Users           -- spell out [database_name].[schema_name].[table_name]
where selectable = 'Y'
GO

-- now we can simply access that view

select username, useremail,country, origins
from vw_EE_Users
-- add in whatever other select qualifiers are needed
where origins like 'country%'

Open in new window


So, you have choices on how to use... Like I said, SQL Server is the relatively more flexible. But that means choices and design considerations. And not without a fair bit of manual effort (code changes). At least with a VIEW, your current web form just needs to access the new name. You could even create the view with the same name as currently used in your webform, but might create confusion, or, conflict with naming conventions.
@Mark Wills,

So when selecting a user from your web form, you access the one user table from that global area.
I see you are not using userid in your solution.

This will make trouble to  historical records ..... You know  we already have many mapped web form ID to evaluator userid (auto generated) in existing database....... How do we retrieve those web form now  without userid  as per your solution ?


select username, useremail,country, origins
from vw_EE_Users
-- add in whatever other select qualifiers are needed
where origins like 'country%'


In existing system  when we save a web form we save  evaluator  userid(auto generated)  along with  the web form ID .  This ensures the mapping which web form would be evaluated by which user.


in your solution what is the mapping ?   ...is it  evaluator  username  with  web form ID ? please clarify ?
You can retain the same ID's Just set identity_insert ON, then populate, then turn it OFF. But that isn't needed in your situation.

If backup and restore then those values already exist so don't have to be recreated.

the ID's would only be created if you were inserting new data. But you aren't repopulating any existing data.

You aren't really changing the data, just where it lives... And accessing it from the new place.

The fact that you don't want a possible conflict (to new userid's) lends support to the notion that USERID may well be global data as a third DB + user table available to both country DB's.

It is also possible to capture the county code and extend what you save to ensure the data can be uniquely identified and will remain unique. So, instead of capturing (unique userID) + (webform ID) you might need to include which DB (ie country code) it belongs to.

You do have choices.
Did you mean this ...



DB1 to have country 1 user ids
DB2 to have country 2 user ids
DB3 to have all user ids ?  

say country 1 user ids is 1 to 99  and  country 2 user ids is 100  to 200
Does DB 3  will hold  1 to 200 userid i.e the entire user records ?

Ok.....now if we add new user in DB1 ,  Do we need add any entry in DB3 and DB2 to avoid any possible conflict ?  if so how what is it ?  

same also for if  if we add new user in DB2 , Do we need add any entry in DB3 and DB1 to avoid any possible conflict ?


So, instead of capturing (unique userID) + (webform ID) you might need to include which DB (ie country code) it belongs to.
you mean to capture  unique userID) + (webform ID) + country code  now ?
Not quite.

If you go down the DB3 route, then that is the only place to keep user specific information (add, change, delete). DB1 can have a view over that data specific to country 1 so only those users are visible inside country 1 DB1 and same thing for DB2. It sounds like FormID might also be used by either country, so that could also be a candidate for "global" data in DB3

OR

add country ID so that CountryID + UserID is a unique representation for that user and then of course you also need FormID. You will need a countryID somewhere regardless of which direction you take, so if it isn't already present, then a good time to add. If you have to add, then it is a legitimate opportunity to introduce that countryID + UserID + FormID to supersede the old UserID + FormID.

BUT

It really depends on what else you do with that user + form combo. Is it updated, does it need follow up, how much / how many other activities are linked. And does country 2 (due to privacy policy) have further interaction with country 1 userid and/or is there a need to generate further transactions across that country delineator from country 2 to country 1.

The problem isn't userID, it is the ensuing transactions, and what country 1 data can be seen or accessible from country 2 (and vice versa)

This is when you need to start drawing up Entity / Attribute diagrams.

The good thing is if you take the time now, it means country 3 / 4 / 5 will be easy to implement.
In your  "OR"  approach,

And does country 2 (due to privacy policy) have further interaction with country 1 userid

Yes.  How do you connect  this interaction ?  country 1 user might view report of  country 2 user and vice versa - read only reports  (no inter territory  transaction)

Here I post the original requirement again..

my requirement is  to segregate user data of country 1 into  DB1 and user data of country 2 into DB2 .
how do I manage existing  references now so that country 1 users still can see country 2 records and vice versa through the application although records  has been separated in two different databases ?

How do you mitigate this in your "OR"  approach ?


is there a need to generate further transactions across that country delineator from country 2 to country 1.

No.  

country 1 user transaction  will be saved in DB1
country 2 user transaction will be saved in DB2
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
cofactor, do you still need help with this question?
There must be something in the current DB that helps identify where that user belongs. We will need that.

Yes. We have that. Its the Nationality of user  which will helps us to identify that part.
do we need to show examples

Yes . Please.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All of us answered the question.

Divide the points among:
* 50 points: mrcoffee365 2015-11-21 at 17:22:13 ID: 41290205
* 400 points: Mark Wills 2015-11-21 at 18:35:26 ID: 41290445
* 50 points: Vitor Montalvão (https:#a41379666)
mrcoffee365,

I'm sorry but I don't agree you answered the question.
There's a statement from the author proving that (ID: 41291424) :

"This will work for views/reports . ...this is fine.....  (since two datbase will now remain in two different data center..so we can do  a DB link and go your way for sure)

 But there is an issue here .....  Its regarding transaction ...see the case below..

 currently when  country 1  user  opens a web form in application we  set evaluator of the form to a country 2 user.....Now if we separate the DB , how do I manage this reference ?


 Also please note we have plan to add  more users to country 1  and country 2 in coming years"
Vitor Montalvão: Sure, we can talk about this all day.  I and others answered his first question.  Then he added more details, and Mark Wills went on to answer all the other questions the asker had.  I thought it was nice to include you in the point list since your response was some links to documentation.