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
cofactorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gurpsbassiCommented:
In memory joins?
or perhaps cross DB joins in SQL Server - which I don't have much experience of.
Jim HornMicrosoft SQL Server Data DudeCommented:
>how .. country 1 users still can see country 2 records and vice versa
Define 'see'.
mrcoffee365Commented:
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark WillsTopic AdvisorCommented:
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.
cofactorAuthor Commented:
>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.
cofactorAuthor Commented:
@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
cofactorAuthor Commented:
@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.
cofactorAuthor Commented:
@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  ?
Mark WillsTopic AdvisorCommented:
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.
cofactorAuthor Commented:
@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
mrcoffee365Commented:
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.
Mark WillsTopic AdvisorCommented:
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.
cofactorAuthor Commented:
@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 ?
Mark WillsTopic AdvisorCommented:
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.
cofactorAuthor Commented:
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 ?
Mark WillsTopic AdvisorCommented:
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.
cofactorAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
I am not sure you are understanding the gravity of that "OR" scenario.

1) You will not be able to retain the same process the same way.
2) Somewhere, somehow you will need to ascertain "which" DB is the target DB
3) USERID as it was, is not enough, you will need some additional information (country or DB)
4) If you don't add that information, you will never retrieve, or, have duplicate USERID
5) If you do add, then you have to retrospectively "fix" all current data with "user"+"form"

Now all that can be done, but it is a big data cleansing and application overhaul just to add in that one seemingly innocuous little bit of data to identify country. Just separating into different databases is not enough because you need to know where to get (and put) that forms related data, and how to monitor it.

I am not trying to be patronizing whatsoever, and understand that the written word doesn't always convey the correct sentiment, and there are those in this thread who will attest to my somewhat terse mannerism. But please be patient, I only have your best interests at heart.

While we can design a scenario that can work such that DB1 owns their own users (let's call that 'user1) and DB2 owns their own users (lets call that 'user2') we end up with 'user2','user1' and without any further information, then where the heck do they belong ? Can you tell me where user3 and user4 belong ? Without additional reference, it is impossible.

So, restating the problem, is not all that helpful, other than there is a conceptual misunderstanding of what is needed to uniquely identify a user so that we definitively know where they belong. Doesn't matter too much within a given DB, but is critical when going across DB boundaries. And that's the real problem.

So, we need to accept that Userid is not the problem, it is associating the userid with the correct DB that is the problem, and the change that must be done if wanting to track data across DB boundaries.

Accessing information (especially read only) is not difficult once we know the correct 'home'. We have proven / demonstrated that with the use of Views and the multipart naming convention to identify an object.

But we do need a map that can show that 'user3' belongs to DB2.

I understand the difficulty, because currently, it is all in one DB and userID's are unique. There must be something in the current DB that helps identify where that user belongs. We will need that.

The 'map' is nothing more than 'this user belongs to DB1 (or DB2)' and quite possibly another number for that user (to reflect a new sequential numbering system within the DB).

That is what I have called 'global data' because it needs to be accessible no matter where you are. It doesn't have to have names or whatever might be deemed sensitive (given the privacy rules), but it must exist and could be considered as DB3.

Sensitive user information will have to stay inside their own DB, but we can have a series of numbers pointing to that. AKA that 'Global Data' or 'Data Map'. And that is about the only place where the current USERID will make sense or be resolvable to the new home.

Its first job will be to help split the database. Whatever information is currently available to help differentiate / identify users belonging to country 1 or country 2 for the basis of the map. Mentioned it above with "We will need that".

Is that making sense ? If not, then what isn't making sense (and please don't retype the question, but ask what we need to clarify). How familiar are you with SQL server and what it can do ? Are you responsible for the DB redesign ?

Or, do we need to show examples ? In which case please state what that extra information is - even if using 'countryID' as a convenience so long as something does exist that identifies how the DB will be split. And, have to say, while we are here to help, this last bit is getting way past the capacity of a single question.
Vitor MontalvãoMSSQL Senior EngineerCommented:
cofactor, do you still need help with this question?
cofactorAuthor Commented:
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.
cofactorAuthor Commented:
do we need to show examples

Yes . Please.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use VIEW to limit accesses. I wrote 3 articles about Views so I recommend you to give a read on them and check if they can be used as solution for your case:
Understanding the Views scope (i) - Limiting the access to data
Understanding the Views scope (ii) - Ownership chaining
Understanding the Views scope (iii) - The complete security layer

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrcoffee365Commented:
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)
Vitor MontalvãoMSSQL Senior EngineerCommented:
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"
mrcoffee365Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.