Is it a good idea to use Synonyms to avoid creating a duplicate table?

Question

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.

Answer 1

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.

Answer 2

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: Decision criteria on when to use a non-dbo schema vs a new Database about "cross-database referential integrity" after restores

Answer 3

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.

Answer 4

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.