Foreign Keys refering to non-existing items
I am not sure how to name my scenario properly apperantly as visible, but here is the situation:
I got multiple tables where data is inside and an application layer that inserts, deletes and updates the data.
A requirement is that i am able to log all changes entirely. To do this, i created a huge Log class that contains all classes that require logging.
The thing is: The log entries are never allowed to be deleted so that, if needed, i can recreate whatever was deleted (requires to identify all log entries for a given deleted type).
Example code:
public class Tag
public int Id get; set;
public string Name get; set;
public class Log
[Key]
public int Id get; set;
[ForeignKey(nameof(TagFK))]
public Tag Tag get; set; }
public int? TagFK get; set; }
}
TL;DR:
- Log Table with FKs
- Log entries need to be always existing while their related entry might get deleted.
c# .net entity-framework ef-code-first
asked Nov 15 '18 at 10:23
X39X39
681515
|
show 2 more comments
I am not sure how to name my scenario properly apperantly as visible, but here is the situation:
I got multiple tables where data is inside and an application layer that inserts, deletes and updates the data.
A requirement is that i am able to log all changes entirely. To do this, i created a huge Log class that contains all classes that require logging.
The thing is: The log entries are never allowed to be deleted so that, if needed, i can recreate whatever was deleted (requires to identify all log entries for a given deleted type).
Example code:
public class Tag
public int Id get; set;
public string Name get; set;
public class Log
[Key]
public int Id get; set;
[ForeignKey(nameof(TagFK))]
public Tag Tag get; set; }
public int? TagFK get; set; }
}
TL;DR:
- Log Table with FKs
- Log entries need to be always existing while their related entry might get deleted.
c# .net entity-framework ef-code-first
asked Nov 15 '18 at 10:23
X39X39
681515
I am not sure I understand your problem fully, but I don't see why you need a FK from the log to the other tables. The log table should be standalone, and independent from the rest.
– Nick
Nov 15 '18 at 10:29
mostly to be able to just usefrom log in dbcontext.Logs where ... select log
– X39
Nov 15 '18 at 10:33
You still will be, but the queries will be (perhaps) slower due to lack of indices.
– Nick
Nov 15 '18 at 10:37
so you would propose to add everything with no real relation but rather adding Indexes on all of those?
– X39
Nov 15 '18 at 10:39
1
Indeed. I would also propose to delay adding indexes until you are certain you need them. In practice, you will be searching for log changes within a certain period of time. You will need an index by the date of the log record, and perhaps on the type of entity being logged. I have implemented a similar case in a CRM system and performance is ok, even tough our log has already more than 1 million records.
– Nick
Nov 15 '18 at 11:18
|
show 2 more comments
I am not sure how to name my scenario properly apperantly as visible, but here is the situation:
I got multiple tables where data is inside and an application layer that inserts, deletes and updates the data.
A requirement is that i am able to log all changes entirely. To do this, i created a huge Log class that contains all classes that require logging.
The thing is: The log entries are never allowed to be deleted so that, if needed, i can recreate whatever was deleted (requires to identify all log entries for a given deleted type).
Example code:
public class Tag
public int Id get; set;
public string Name get; set;
public class Log
[Key]
public int Id get; set;
[ForeignKey(nameof(TagFK))]
public Tag Tag get; set; }
public int? TagFK get; set; }
}
TL;DR:
- Log Table with FKs
- Log entries need to be always existing while their related entry might get deleted.
c# .net entity-framework ef-code-first
asked Nov 15 '18 at 10:23
X39X39
681515
I am not sure how to name my scenario properly apperantly as visible, but here is the situation:
I got multiple tables where data is inside and an application layer that inserts, deletes and updates the data.
A requirement is that i am able to log all changes entirely. To do this, i created a huge Log class that contains all classes that require logging.
The thing is: The log entries are never allowed to be deleted so that, if needed, i can recreate whatever was deleted (requires to identify all log entries for a given deleted type).
Example code:
public class Tag
public int Id get; set;
public string Name get; set;
public class Log
[Key]
public int Id get; set;
[ForeignKey(nameof(TagFK))]
public Tag Tag get; set; }
public int? TagFK get; set; }
}
TL;DR:
- Log Table with FKs
- Log entries need to be always existing while their related entry might get deleted.
c# .net entity-framework ef-code-first
c# .net entity-framework ef-code-first
asked Nov 15 '18 at 10:23
X39X39
681515
asked Nov 15 '18 at 10:23
X39X39
681515
asked Nov 15 '18 at 10:23
X39X39
681515
asked Nov 15 '18 at 10:23
X39X39
681515
asked Nov 15 '18 at 10:23
X39X39
681515
681515
I am not sure I understand your problem fully, but I don't see why you need a FK from the log to the other tables. The log table should be standalone, and independent from the rest.
– Nick
Nov 15 '18 at 10:29
mostly to be able to just usefrom log in dbcontext.Logs where ... select log
– X39
Nov 15 '18 at 10:33
You still will be, but the queries will be (perhaps) slower due to lack of indices.
– Nick
Nov 15 '18 at 10:37
so you would propose to add everything with no real relation but rather adding Indexes on all of those?
– X39
Nov 15 '18 at 10:39
1
Indeed. I would also propose to delay adding indexes until you are certain you need them. In practice, you will be searching for log changes within a certain period of time. You will need an index by the date of the log record, and perhaps on the type of entity being logged. I have implemented a similar case in a CRM system and performance is ok, even tough our log has already more than 1 million records.
– Nick
Nov 15 '18 at 11:18
|
show 2 more comments
I am not sure I understand your problem fully, but I don't see why you need a FK from the log to the other tables. The log table should be standalone, and independent from the rest.
– Nick
Nov 15 '18 at 10:29
mostly to be able to just usefrom log in dbcontext.Logs where ... select log
– X39
Nov 15 '18 at 10:33
You still will be, but the queries will be (perhaps) slower due to lack of indices.
– Nick
Nov 15 '18 at 10:37
so you would propose to add everything with no real relation but rather adding Indexes on all of those?
– X39
Nov 15 '18 at 10:39
1
Indeed. I would also propose to delay adding indexes until you are certain you need them. In practice, you will be searching for log changes within a certain period of time. You will need an index by the date of the log record, and perhaps on the type of entity being logged. I have implemented a similar case in a CRM system and performance is ok, even tough our log has already more than 1 million records.
– Nick
Nov 15 '18 at 11:18
I am not sure I understand your problem fully, but I don't see why you need a FK from the log to the other tables. The log table should be standalone, and independent from the rest.
– Nick
Nov 15 '18 at 10:29
I am not sure I understand your problem fully, but I don't see why you need a FK from the log to the other tables. The log table should be standalone, and independent from the rest.
– Nick
Nov 15 '18 at 10:29
mostly to be able to just use
from log in dbcontext.Logs where ... select log– X39
Nov 15 '18 at 10:33
mostly to be able to just use
from log in dbcontext.Logs where ... select log– X39
Nov 15 '18 at 10:33
You still will be, but the queries will be (perhaps) slower due to lack of indices.
– Nick
Nov 15 '18 at 10:37
You still will be, but the queries will be (perhaps) slower due to lack of indices.
– Nick
Nov 15 '18 at 10:37
so you would propose to add everything with no real relation but rather adding Indexes on all of those?
– X39
Nov 15 '18 at 10:39
so you would propose to add everything with no real relation but rather adding Indexes on all of those?
– X39
Nov 15 '18 at 10:39
1
1
Indeed. I would also propose to delay adding indexes until you are certain you need them. In practice, you will be searching for log changes within a certain period of time. You will need an index by the date of the log record, and perhaps on the type of entity being logged. I have implemented a similar case in a CRM system and performance is ok, even tough our log has already more than 1 million records.
– Nick
Nov 15 '18 at 11:18
Indeed. I would also propose to delay adding indexes until you are certain you need them. In practice, you will be searching for log changes within a certain period of time. You will need an index by the date of the log record, and perhaps on the type of entity being logged. I have implemented a similar case in a CRM system and performance is ok, even tough our log has already more than 1 million records.
– Nick
Nov 15 '18 at 11:18
|
show 2 more comments
1 Answer
1
active
oldest
votes
Create a trigger a DB to add entry into log table before insert update and delete.
Use a user to connect to DB which only has insert access to the log table and not delete or update.
Better to create a log table per table if you really want to restore at some time.
answered Nov 15 '18 at 10:29
binrootbinroot
111
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53317250%2fforeign-keys-refering-to-non-existing-items%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Create a trigger a DB to add entry into log table before insert update and delete.
Use a user to connect to DB which only has insert access to the log table and not delete or update.
Better to create a log table per table if you really want to restore at some time.
answered Nov 15 '18 at 10:29
binrootbinroot
111
add a comment |
Create a trigger a DB to add entry into log table before insert update and delete.
Use a user to connect to DB which only has insert access to the log table and not delete or update.
Better to create a log table per table if you really want to restore at some time.
answered Nov 15 '18 at 10:29
binrootbinroot
111
add a comment |
Create a trigger a DB to add entry into log table before insert update and delete.
Use a user to connect to DB which only has insert access to the log table and not delete or update.
Better to create a log table per table if you really want to restore at some time.
answered Nov 15 '18 at 10:29
binrootbinroot
111
Create a trigger a DB to add entry into log table before insert update and delete.
Use a user to connect to DB which only has insert access to the log table and not delete or update.
Better to create a log table per table if you really want to restore at some time.
answered Nov 15 '18 at 10:29
binrootbinroot
111
answered Nov 15 '18 at 10:29
binrootbinroot
111
answered Nov 15 '18 at 10:29
binrootbinroot
111
answered Nov 15 '18 at 10:29
binrootbinroot
111
111
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53317250%2fforeign-keys-refering-to-non-existing-items%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I am not sure I understand your problem fully, but I don't see why you need a FK from the log to the other tables. The log table should be standalone, and independent from the rest.
– Nick
Nov 15 '18 at 10:29
mostly to be able to just use
from log in dbcontext.Logs where ... select log– X39
Nov 15 '18 at 10:33
You still will be, but the queries will be (perhaps) slower due to lack of indices.
– Nick
Nov 15 '18 at 10:37
so you would propose to add everything with no real relation but rather adding Indexes on all of those?
– X39
Nov 15 '18 at 10:39
1
Indeed. I would also propose to delay adding indexes until you are certain you need them. In practice, you will be searching for log changes within a certain period of time. You will need an index by the date of the log record, and perhaps on the type of entity being logged. I have implemented a similar case in a CRM system and performance is ok, even tough our log has already more than 1 million records.
– Nick
Nov 15 '18 at 11:18