locked
Centralized data concepts RRS feed

  • Question

  • 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.

    Sunday, May 6, 2012 10:34 PM

Answers

  • Are  you trying to save DBA's work :-))) How much data does transfer from the central to the app? How much app database does the instance have?If your "problem" are PK/FK you cannot use views in that way.... I would go for scheduling job to move the data over...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 8, 2012 5:52 AM

All replies

  • Do centralized  and app databases locate on the same server? Is that possible to populate the data in app's database directly from central  database, means no views m just truncate and insert into a table?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, May 7, 2012 8:22 AM
  • Yes the app databases are on the same SQL server instance. It is possible I can have scheduled stored procedures to truncate and copy sub sets of the centralized data to the application databases. I was trying to avoid this because from the dba perspective it adds more to managing these scheduled jobs as well as using more physical storage and increased execution time for maintenance tasks like backups. Whereas using a view to the central database the dba only has to monitor the SSIS jobs to populate the centralized data and not the scheduled jobs to distribute the data. But the views have their technical limitations as described in my original post.

    It sounds more like a "who is going to be burdened with more work" argument but I am trying to figure out if there are some established practices that are generally agreed upon in these types of environments.

    Thanks

    Monday, May 7, 2012 3:14 PM
  • Are  you trying to save DBA's work :-))) How much data does transfer from the central to the app? How much app database does the instance have?If your "problem" are PK/FK you cannot use views in that way.... I would go for scheduling job to move the data over...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 8, 2012 5:52 AM