sql - Using same table in many database -

sql - Using same table in many database -

i'm big fan of info integrity when comes database. way of thinking :

it's improve enforce constraint straight in database (example : foreign key) either waiting application crash because row didn't exist in table or manually adding constraint everywhere in code of application.

the problem

we have few tables duplicated in multiple databases , used multiple applications. 1 illustration user table.

we have users in applications. issue users treated separately @ moment while should, @ to the lowest degree think, grouping together. currently, there many duplicates on place , outdated info user everywhere. when user updated in 1 database/application not updated in other one, it's same user info should updated everywhere.

what planning do

we thinking creating reference database regroup information. example, info users store in same database , every application utilize database access info need user.

question 1 : idea? there other alternative avoid duplicate/outdated info on place?

the new problem

while grouping user info single database prepare problem of duplicate/outdated user information, create new problem info integrity :

we can no longer create foreign key constraint on user table since it's located in database.

sure, user tables easy access views can't apply foreign key constraint on view either...

question 2 : so, options if maintain info integrity constraints straight database?

question 1 : idea? there other alternative avoid duplicate/outdated info on place?

the problem facing isn't unique. applications have been designed ages operate within own scope, own own info , "self contained." industry has realized cost of maintaining separate systems, , value of info quality, they've striven improve quality , cut down overhead. you're getting reason have n-tier development , mutual company specific code. illustration service or "dll" obfuscates info layer allowing developer unaware of database while gaing command on mutual information. idea; if you're company growing, can't afford not to.

alternative identify single authoritative source , replicate info other sources; or require sub systems study authoritative when alter information, managing conflicts if info changed in both places.

question 2 : so, options if maintain info integrity constraints straight database? identification of authoritative source , replication between applications. ensure updates made in sub systems propagate master , master propagates children

still require unique identifier exist in various systems allowing manage integrity; have "associated values" costly maintain in authoritative source. consider requiring association between user , application in user database add together layer of security systems. , note when users in, or no longer in scheme (not existence of record start , end dates. no end date user still has access application.

sql sql-server