Solved

Moving from a table to a view

Posted on 2011-09-26
6
158 Views
Last Modified: 2012-05-12
I have a table that contains user info.  It has a primary key of userid.  I have foreign key constraints in various other tables that reference the userid.   These tables and database are for new applications.

Now, there are legacy apps also use a table of users.  I need to make the legacy apps and new apps use the same list of users.  The legacy apps use one database and the new apps use another, though on the same server.  So, I've created a view in the new apps database that pulls in the necessary user columns from the legacy apps database.  

Now, prior to this, and for development purposes only, I had a temp user table in the new apps database, and I had created foreign key constraints.  Now, if I go with the view idea that I describe above, and remove the users table in the new apps database, I lose referential integrity enforced by the foreign keys, correct?

Questions:  Is this a reasonable approach, to get the two sets of app to use the same set of user data?  If I name the view the same as the user table and use the same column names, will this change be transparent?   Is the loss of referential integrity a small price to pay?  Any suggestions on how to better handle this?  
0
Comment
Question by:HLRosenberger
  • 3
  • 2
6 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36671471
I am not sure that I completely understand your question.  Referential integrity is enforced at the table level, and I don't quite understand what changes you would be making to the tables.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 36674470
I would lose the Referential integrity for the userid's in the new database because I'd now create a view in the new database that would pull in the data from the old database.

The new database has other tables that currently have foreign keys to the temporary user table - which would go now go away and be replaced by the new view.  

Did I articulate this properly?

 
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 36675203
Are you saying that you have a linked server, and the view would be referencing data from both databases, without foreign key relationships?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 36678363
Not linked.  Two databases on the same server.
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36709143
It's not a bad way to go.  It would also be nice if you could have cross database foreign key relationships, then you wouldn't need the view, but that's not an option.  You're exactly right, though.  You are sacrificing referential integrity.  To know the impact of this, you would have to look at everything that references the view.  The danger is that something could be modified that would violate that constraint.  Like a using a userid that isn't in the view.  Most likely, you are probably safe.

Another way to go is to make the view a table so that you can still have referential integrity.  To implement this, you would have to create a stored procedure to synch up the data.  And then run that procedure periodically through a job.  If you only insert and update data in the original users table, this should be fairly easy.  I'm assuming that you set some sort of active flag instead of deleting.  That's always how I setup my users tables because you can't delete users without deleting all of the data associated with them.

Hope this makes sense.  If not, please ask questions.

Greg
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 36893424
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
SQL Server 2008 to SQL Server 2016 Replication 8 54
In or Between 2 44
Managing Columnstore Indexes 2 19
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now