Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>When discussing auditing, I would go back to the purpose behind it. It isn't really a backup but rather a history of what has been. For example, for <code>StudentScore</code>, you would want to be sure not to lose the fact that the student originally had a 65% when they now have a 95%. This audit trail would allow you to walk back through the changes to see what happened and who did it. From this, you could identify what a particular user did to abuse the system. In some ways this could be a type of backup since you could roll back these changes to their previous states without rolling back entire tables.</p> <p>With this in mind (if my assumptions about what you are using this for are correct), the only place you would want a FK/PK relationship is between the history table and its "live" counterpart. Your audit (history) table should not refer to any other table because it is no longer a part of that system. Instead, it is simply a record of what has happened in one table. Period. The only referential integrity you might want to consider is between the history table and the live table (thus the possible FK/PK relationship). If you allow records to be deleted from the live table, don't include the FK in the history table. Then the history table could include deleted records (which is what you want if you allow deletions).</p> <p>Don't get confused with relational integrity in the main database with this history table. The history tables are all stand-alone. They only serve as a history of one table (not a set of tables). </p> <p>The relating of two history tables together is possible and even more advanced relations between the live and history tables together (Students and Courses with both live and history, for example) so you can deal with even the possibility that a student was deleted (shudder) since the record would still be in the history table. The only issue here would be if you don't keep the history for a particular table, in which case you are choosing to lose that data (if you allow deletes).</p>
    singulars
    1. This table or related slice is empty.
    1. POAudit Tables: Maintaining Referential Integrity - Good or Bad
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USIAmTimCorey
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAudit Tables: Maintaining Referential Integrity - Good or Bad
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYes - this question ignores the distinction between relational data and a log file. No point applying RDB structures and requirements - but a log file has its own structures and requirements.
      singulars
      1. PO
      1. USdkretz
    2. COAgree with your points, other than 'relating of two history tables is possible' - couldn't see how can we implement it. There might be multiple CourseID:15 in CourseHistory table - what would we be maintaining the FK agains. Also, any suggestions on what could be potential drawbacks (performance, etc) in case we _do_ maintain the FK between StudentHistory & Course table. Thanks for your time!
      singulars
      1. PO
      1. USYetAnotherUser
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload