Distributed Environment - Replicated (staging) Database (deleting FK rows)

Hi all,

Hoping someone could please answer this. What is the best practice for handling this situation?

I am trying to create a system that will let me specify future changes on a set of entity objects (staging area). I was thinking the best way to do this would be to have a copy of the production database (just the schema), which I could then use my already developed application against in order to set properties on a given object and then create a method to push those changes back into the production database at a later time.

I am trying to do this in an object oriented manner, I know that I can detach an object from one context and attach it to another, so I can push the objects back and forth between the two databases. The question is, how do I keep all of the FK contraints and referential integrity between the two databases intact?

For instance:

I have a set of tables that have columns that reference foreign keys from other tables.

One such table:

'DictionaryOrganSystem' with 2 fields.

1) row ID (primary key)

2)Organ System Name

I also have a table called 'OrganSystems' which is used as a 1-M table linking 'Tests' to organ systems and has 3 columns:

1) row ID (primary key)

2) TestInformationID (FK link to the TestInformation Table) <--- this column is not relevant to the discussion.

3) DictonaryOrganSystemID (FK link to the DictionaryOrganSystem table)

What I would like to know is the best practice for avoiding exceptions in the entity framework and SQL when a row is deleted from the DictionaryOrganSystem table? I have the rule set to cascade, which means that the associated row in the OrganSystems table will be deleted automatically in the production database. But what about the tables/dictionaries in the "secondary database" that should somehow have a working knowledges of the changes that occured in the production system. I would think that this cascade should follow down to the secondary database as well from the production database.. can this be handled with triggers? Otherwise the method I develop to push the changes from the staging database to the production databse will cause an exception when adding a row that references the deleted DictionaryOrganSystem row.

I am not sure the best practice of having a production database which has a "staging" area for future changes. Does anyone have a good article to reference?

Thank you so much!

Thanks,
Gene

Re: Distributed Environment - Replicated (staging) Database (deleting FK rows)

Take a look at database projects

http://msdn.microsoft.com/en-us/library/ff678491.aspx

Re: Distributed Environment - Replicated (staging) Database (deleting FK rows)

Hello ef305804

If I understand you correctlyYou want a database and do many modifications to that data contents and then back them up automaticallySo as to make the two databases sychronizedAm I right

If soI think you can do modifications to the certain databasesand then at a certain peroidplease set up something like back-up to back the whole database as a "full copy" after removing the older versionyou can always make your two database "same" and "sychronized"

Reguards

Re: Distributed Environment - Replicated (staging) Database (deleting FK rows)

Hello ef305804

First I was told that you couldn't replySo what's up now

I am trying to find a way to define "Future Changes" to database objects. I was thinking that creating a second database with the same exact schema as the production data (minus all of the data) would serve as an area to hold the modified objects from the production database. These changed objects would then overwrite the objects in the production database at a future specified date.

Sort of like a "Staging Area", so that users can use my application to define a change right now, and have it actually happen at a future time. I was thinking this could be done by detaching the old entity object from the production context and re-attaching the new one that was defined in the staging database.

Then I get into the issues of syncing the two databases FK objects as specified in the original post.

Any help would be greatly appreciated!

YesI think your ideatheorily speakingit's very nicehowever I think it's hard to do that

You are "future viewing" the structureand if your db changes a lotI think the schema cannot be definedHow

Until nowas far as I seeNo matter when you use the general ADO.NET, EF or LINQ-TO-SQLYour mapping real datatables should be always fixed one.

Kindly correct me if I take you wrong