May 6, 2012 at 3:12 am
Hello,
I am working on creating a centralized database for my organization. The app developers currently make a new database for each app they create and ask me the populate their app with data. This data typically comes from Oracle into SQL server via SSIS scripts directly into their app database. Over time of course the SSIS downloads become redundant, most of the time the different applications are asking for similar data (user profile and demographic information on users).
So I want to put all the downloaded Oracle data into one database and all of the locally collected application specific data would be left in each respective application database.
So to integrate my centralized data with existing apps I wanted to create views that are sub sets of the centralized data to replace some of the tables in the app database.
1st issue I run into is with foreign keys. As they should be doing, the developers are adding foreign key relationships to enforce data integrity among their tables. When I replace one of the tables with a view to the centralized data I cannot reference that view in a foreign key. Some google searching pretty much says that foreign keys have to be created on tables and can't be created to reference a view, period.
Creating the foreign key to reference the underlying table that the view is based on won't work because the view is filtering the table down to a specific subset and those are the only values the foreign key should allow.
Some alternatives such as creating 'instead of' triggers to enforce integrity have other functional downsides such as the database diagram won't draw the links between the table and the views because the keys aren't defined. Also, the app developers use entity model framework. While it is possible to manually create the relationships between the view and the tables in the entity model, it would be much easier on them if they could use the "database first" modeling method where the relations automatically show up in the entity model that are defined on on the database tables. It would be hard to get them to buy-in to a system that creates more work for them on every model they create.
I know that a lot of other people out there have centralized systems feeding satellite systems. How are you people handling issues like this? Do you use views and if so how do you handle foreign keys to the central data? Or do you have synchronization jobs that physically copy the central data to the app databases? Seems like the main concept of reducing data redundancy is voided if you have to replicate the central data to be stored into each app database.
Any design suggestions would be appreciated.
May 7, 2012 at 6:35 am
Hi,
We have a centralized repository of shared data and we send the shared data to local apps as required. The applications themselves maintain the data that is specific to them (i.e. not shared). I believe that this sounds similar to what you are proposing.
If I understand you correctly, the views sound redundant because you're just creating them to do a SELECT * FROM VIEW to populate your applications with the data from the central database? Could you not code the same conditions/logic into a different mechanism (web service, SSIS package, Biztalk, etc) to feed the individual applications as required?
In our case, because we had a complex layout of disparite apps, on different systems that didn't integrate well, we centralized integration itself as an enterprise level task. So, the integration system became a collection of components, managed as a unit, to coordinate data. It is separate from, and provides services to, our enterprise applications. The business rules governing what data each application sees and how it sees it are kept "in the pipe" so they can be managed centrally.
It doesn't sound like your apps will actually use the views to read data, just to stage it in the way you need it to download it. So, I'd skip the view approach entirely and just load the apps from the central database using the best method for each application to consume.
May 7, 2012 at 10:09 am
Why not start a project to move the data into a single database?
The Oracle downloaded data could be put into one schema, and each developer or whatever is necessary could be given his or her own schema to manage. They could create foreign keys from tables in their schema to the Oracle driven tables as well.
It would probably leave you to manage the ETL processes that fill the Oracle tables, but that's a small price to pay overall.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply