Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we have to update 3 databases.

Would it be a better idea to delete the Users table from databases 2 and 3 and replace it with a Synonym that points to database 1?

Here's the Pros/Cons I can think of:

Pros

  • Easier Maintenance. Can update Users in one location instead of 3
  • User Ids would match between databases (Important since a lot of add-on apps are based on UserId)

Cons

  • Don't think this is standard procedure, so might be confusing
  • Users would have to have identical settings between databases
  • (From gbn's answer below) If Database 1 ever goes down, Database 2 and 3 will also be unavailable. Also there is the potential problem of the data being inconsistent in the event of a restore

This is an option I'm considering for a few different tables containing Settings that are identical between the databases, not just the Users table. I am using Users in the example since it is easy to understand.

share|improve this question

migrated from programmers.stackexchange.com Feb 14 '12 at 14:53

This question came from our site for professional programmers interested in conceptual questions about software development.

3  
Would it make more sense to have a script to synchronize/merge the differences between them? I personally don't like making 2 databases depend on a third because of a synonym like this. –  FrustratedWithFormsDesigner Feb 14 '12 at 14:58
    
@FrustratedWithFormsDesigner That seems like much more work, and it's hard to merge changes to auto-generated fields such as a primary key. –  Rachel Feb 15 '12 at 15:34
    
It depends on how fancy you want to get. If you want to share changes between them, then yes it can get complicated. If you want to designate one as the Master, then it is simply a matter of overwriting the contents of the others with the contents of the Master. –  FrustratedWithFormsDesigner Feb 15 '12 at 15:38
    
@FrustratedWithFormsDesigner Unfortunately the application is closed source and there is nothing to stop people from adding or editing users in any Database. I would have to make the changes two-way since everyone has the ability to change their own password, and almost all managers have access to the user management to add/edit users. –  Rachel Feb 15 '12 at 15:56
add comment

4 Answers

Why do you have 3 databases with the same users?

What happens if one database goes down now?

I'm asking these questions because the Con of the user-source database going down preventing use of the other two databases may not be as big a problem as it sounds.

Additionally, if a database goes down now you will already have consistency problems if users are modified in the other two DBs during that period. I don't think we can give the best advice without more context.

Right now I would create a fourth database for users, make changes only there, and sync across the other databases. You're already distributed-denormalized by changing essentially the same data in three places, and probably already have consistency problems. If fault tolerance is important then this scheme still gives that while solving the multiple entry problem.

I think that having this fourth user/settings-only database rather than using one of your existing ones is a good strategy as it becomes less likely to go down since it isn't tied to other resources or heavily used. I see now that you said you can't do this, but I'm not clear on why. Do the applications on the main database support user editing directly, or is user editing a completely separate function that can be pointed elsewhere?

True, with this "canonical user table" idea--whether you use synonyms or synchronize the data--you can't modify users during a down user DB, but that seems ok to me. Fix the problem and bring it up! One source, one place to edit, one thing to fix if broken. With synchronization, all other databases have temporary copies they can work from, but no editing. Minimizing data duplication across systems is a great and useful goal. It is a serious problem to enter the same data in three places, so do whatever you can to eliminate that.

To address more of your comments, if your user IDs are different across all your applications, then you should seriously consider a planned down time for your two new databases to bring them in sync with the main one (ask a separate question if you need ideas on how to accomplish this, which is tricky but not really THAT hard).

If you analyze your business needs and find that the main database must have the user data that's okay, you still use it as canonical, then choose between synonyms or synchronizing to address how unplanned down times affect all the databases.

One additional Con to using synonyms is that you would no longer be able to have proper FKs to Users in each database.

share|improve this answer
add comment

There is a missing "Con"

On restore, it is possible that your user won't exist in the target (of the synonym) database. Say it's corrupted, and you have to restore from a backup.

That is, the use of synonyms will assume cross-database referential integrity which can't happen in real life.

Another consequence of this would be mismatched userids when you try to recover from this. (by adding the user after restore). However, this should never be assumed anyway because of cross-database referential integrity I mentioned.

So, don't do it...

A related answer on dba.se: http://dba.stackexchange.com/q/11101/630 about "cross-database referential integrity" after restores

share|improve this answer
    
Good point on the possibility the target doesn't exist, although I am still trying to figure out why the link is related to this question –  Rachel Feb 14 '12 at 14:16
1  
@Rachel: no, target table can exist but data may be older. So when you restore you have a missing user... The link is about "cross-database referential integrity" –  gbn Feb 14 '12 at 14:19
add comment

I would create a 4th DB that stores the shared User Info. Create Synonyms or Views in each of the Other Dbs pointing to the Users DB.

Lastly i would create an extension table (a table with a One-one relationship with the 'UserDB.Usertable') in each of the 3 existing dbs that holds user data specific to that app.


Edit : Based on the comment below

In that case make the First db act as the Master user table. Synonyms and an extension table in each of the Other Dbs.

Important note: With this approach, If your first app goes down, the other two are pulled down with it! Make sure everyone understand this drawback.

share|improve this answer
    
Sadly that is not an option for me. It would be my preferred option if I were designing something from scratch, but in this case I am working with a pre-existing system. In my case, we had Database 1 which existed for many years, and have recently added Databases 2 and 3. –  Rachel Feb 14 '12 at 14:05
    
Why can delete the tables and point the Synonym to the first db but not to a new one? –  Morons Feb 14 '12 at 14:07
    
Sorry, edited my comment before I saw yours. Database 1 has existed for many years and is accessed by many external apps. I am unsure of what kind of damage I'd cause by removing that table. Databases 2 and 3 are newer so I think I can get away with removing the Users table (and other settings tables) and replacing them with synonyms –  Rachel Feb 14 '12 at 14:15
    
I see your edit... that's exactly what I am trying to do but my question was is this a good idea or not, and if not, why? –  Rachel Feb 14 '12 at 14:38
    
Its fine. If you are ok making the 2 news apps fully dependent on the first app. –  Morons Feb 14 '12 at 14:41
show 1 more comment

Depending on your database platform, another option is to delete the tables in databases 2 and 3 and replace them with materialized views of the table in database 1.

Pros

  • Easier Maintenance (Of the data)
  • Matching Ids
  • Outages don't affect other databases (at least until changes need to be made)
  • Any database can be used to recover the table.

Cons

  • Data is only as up to date as your refresh schedule.
  • If the table definition changes the materialized views might need to be changes as well.

Also note that depending on the frequency of look-ups, frequency of refreshes, and frequency of data change, this may or may not introduce more load than a synonym solution.


Update: FrustratedWithFormsDesigner's comment is essentially a Materialized View for platforms that can't do materialized views. Using a script the tables could be periodically synchronized. If one database is designated as the master then all scripts could make their changes based on it. This would have all the pros and cons of a materialized view; it just couldn't take advantage of built-in functionality.

share|improve this answer
1  
You can't materialise views (indexed views here) cross-db in SQL Server + they don't need refreshed: they are "real time" –  gbn Feb 14 '12 at 18:26
    
@gbn My post was written before the SQL Server tag was added. –  Leigh Riffel Feb 14 '12 at 19:56
    
I added the tag based on your answer :) –  Rachel Feb 14 '12 at 21:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.