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?
If I understand you correctly——You want a database and do many modifications to that data contents and then back them up automatically——So as to make the two databases sychronized？Am I right？
If so，I think you can do modifications to the certain databases，and then at a certain peroid，please set up something like back-up to back the whole database as a "full copy" after removing the older version，you can always make your two database "same" and
"sychronized"。
First I was told that you couldn't reply……，So 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!
Yes，I think your idea——theorily speaking，it's very nice；however I think it's hard to do that：
You are "future viewing" the structure，and if your db changes a lot，I think the schema cannot be defined。How？
Until now——as far as I see，No matter when you use the general ADO.NET, EF or LINQ-TO-SQL……Your mapping real datatables should be always fixed one.
ef305804
0 Points
2 Posts
Distributed Environment - Replicated (staging) Database (deleting FK rows)
Apr 26, 2012 03:46 PM|LINK
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
distributed FK entity sql
Ken Tucker
All-Star
16797 Points
2608 Posts
MVP
Re: Distributed Environment - Replicated (staging) Database (deleting FK rows)
Apr 27, 2012 10:41 AM|LINK
Take a look at database projects
http://msdn.microsoft.com/en-us/library/ff678491.aspx
distributed FK entity sql
Space Coast .Net User Group
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Distributed Environment - Replicated (staging) Database (deleting FK rows)
Apr 28, 2012 01:14 AM|LINK
Hello ef305804 ；）
If I understand you correctly——You want a database and do many modifications to that data contents and then back them up automatically——So as to make the two databases sychronized？Am I right？
If so，I think you can do modifications to the certain databases，and then at a certain peroid，please set up something like back-up to back the whole database as a "full copy" after removing the older version，you can always make your two database "same" and "sychronized"。
Reguards！
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Distributed Environment - Replicated (staging) Database (deleting FK rows)
May 03, 2012 04:36 AM|LINK
Hello ef305804：）
First I was told that you couldn't reply……，So what's up now？
Yes，I think your idea——theorily speaking，it's very nice；however I think it's hard to do that：
You are "future viewing" the structure，and if your db changes a lot，I think the schema cannot be defined。How？
Until now——as far as I see，No matter when you use the general ADO.NET, EF or LINQ-TO-SQL……Your mapping real datatables should be always fixed one.
Kindly correct me if I take you wrong……