Or is there a better way to setup a history, the setup explained earlier was going to be used in all models
The history records should be stored in a separate table and populated via triggers on the active table. If this approach is not possible because of business constraints, you could try the following. Instead of inserting a new record, insert a copy of the
existing active record with its status changed to history, then update the existing active record with the values for the new record. Here's an example:
I was thinking about doing it that way but i would litterly have doube the tables, or two identical databases which I didn't think would be very efficient
Actually, a separate history table would be very efficient. Assuming a 9-to-1 average ratio between history records and active records, your active table would be 1/10th its current size. Since most of your queries would be acting against the active records,
you would see a substantial performance boost. Also, if the history records are simply for auditing purposes, you can periodically archive older data to reclaim space. Here's the documentation on triggers:
However, as I said before, if this is not a viable approach in your situation, I would recommend you try the method outlined in the StackOverflow thread I posted. It would require no change to your current architechture, only a change in the way you "insert"
new records.
Hi, sorry I am still fairly new, i get the size issue, and I am planning on archiving older information on a yearly basis anyway, although i have never done it so i am unsure how it will work, there will be millions of records in the DB, but if I am filtering
the searches anyway using where Active there would there still be substancial benefits to putting the history in a different database or seprate tables?
(it might be more viable, I am still building but I have got so far going back and changing it all would be a pain....)
Do you have and examples or tutorials on triggers, they look interesting but the docs are meaningless to me unless i can see working examples.
If I setup a one to many relationship with Navigation properties, does the navigation property have to be primary key?
Hello：）
As far as I know, I don't think so. You can just create two tables in the SQL Mansgement Studio and create relationship between them by using Diagram in the db, and then drag and drop the two tables into the edmx file, where you can get the whole realted
table schema. And then you can do inserting like this——
chohmann
Star
9385 Points
1644 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 12, 2011 11:31 PM|LINK
The history records should be stored in a separate table and populated via triggers on the active table. If this approach is not possible because of business constraints, you could try the following. Instead of inserting a new record, insert a copy of the existing active record with its status changed to history, then update the existing active record with the values for the new record. Here's an example:
http://stackoverflow.com/questions/1454805/snapshot-history-with-entity-framework
EnenDaveyBoy
Participant
1465 Points
1146 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 12, 2011 11:49 PM|LINK
I was thinking about doing it that way but i would litterly have doube the tables, or two identical databases which I didn't think would be very efficient
(plus I don't know how to write triggers)
chohmann
Star
9385 Points
1644 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 13, 2011 03:05 AM|LINK
Actually, a separate history table would be very efficient. Assuming a 9-to-1 average ratio between history records and active records, your active table would be 1/10th its current size. Since most of your queries would be acting against the active records, you would see a substantial performance boost. Also, if the history records are simply for auditing purposes, you can periodically archive older data to reclaim space. Here's the documentation on triggers:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
However, as I said before, if this is not a viable approach in your situation, I would recommend you try the method outlined in the StackOverflow thread I posted. It would require no change to your current architechture, only a change in the way you "insert" new records.
EnenDaveyBoy
Participant
1465 Points
1146 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 13, 2011 10:16 AM|LINK
Hi, sorry I am still fairly new, i get the size issue, and I am planning on archiving older information on a yearly basis anyway, although i have never done it so i am unsure how it will work, there will be millions of records in the DB, but if I am filtering the searches anyway using where Active there would there still be substancial benefits to putting the history in a different database or seprate tables?
(it might be more viable, I am still building but I have got so far going back and changing it all would be a pain....)
Do you have and examples or tutorials on triggers, they look interesting but the docs are meaningless to me unless i can see working examples.
EnenDaveyBoy
Participant
1465 Points
1146 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 13, 2011 03:21 PM|LINK
http://stackoverflow.com/questions/1454805/snapshot-history-with-entity-framework
I like the sound of that but I have no idea how to set that up, do you have a link to an example or tutorial?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 14, 2011 03:05 AM|LINK
Hello：）
As far as I know, I don't think so. You can just create two tables in the SQL Mansgement Studio and create relationship between them by using Diagram in the db, and then drag and drop the two tables into the edmx file, where you can get the whole realted table schema. And then you can do inserting like this——
Category category = de.CategorySet.Where(ct => ct.CategoryID == id).First();
article.Categories.Add(category);
See more at：http://www.mikesdotnetting.com/Article/109/ASP.NET-MVC-Entity-Framework-One-to-Many-and-Many-to-Many-INSERTS
chohmann
Star
9385 Points
1644 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 14, 2011 05:29 PM|LINK
Something like this (not tested):
public ActionResult Insert(oneTable ot) { oneTable active = db.oneTables.Where(x => x.Status == "active").Single(); oneTable old_ot = new oneTable { oneTableName = active.oneTableName, oneTableGroup = active.oneTableGroup, Status = "history"}; db.oneTables.Add(old_ot); db.UpdateModel(active); db.SaveChanges(); }EnenDaveyBoy
Participant
1465 Points
1146 Posts
Re: Entity Framwork / Navigation Property / SQL issue
Jul 16, 2011 01:22 AM|LINK
Thanks all I think i have it working.
I would like to see a Wiki type tutorial, cos I am a bit rubbish as this and would need to see more info, but I will look into it later.
Thanks again