Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you are going to use that versioning system, I recommend that you also create views to get all the records that are active and have the user interface always use the views (except where they are looking at changes) not directly access that table. I would also put a trigger on the tables to make sure one and only one record is marked as active. Then you have the issue of the unique key and the key to identify the record in the PK/FK relationships (you don't want to have to change this every time you add another record). </p> <p>Alternatively, you could set up audit tables if you just need to go back to old data if you need to research a change. If you need to see history frequently audit tables can be hard to query as they don't usually have the whole record structure just the old and new values, the column name and some meta data like when the change was made and by whom. </p> <p>Possibly you could have a history table that is exactly like the orgional table with a few extra columns (for when the change was made and by whom as well a new surrogate key) and taking the autonumbering and unique index off the orginal PK. Then the active records are all in one table, the inactive records are in another table (populated by trigger) and you use views only when you want to see both sets of data together.</p> <p>The key to all of this is to use triggers to populate the history wherever you intend to store it. Databses are not just affected by the user application, so you need to make sure all changes to the database are recorded not just those done by the user interface or you will have gaps in your history. I can't speak for all databases but in SQl Server, triggers act once on each batch of data, don't write them assuming they will act one record at a time if someone updates a roup of prices in one query. It is generally critical to write triggers in a set-based fashion. Looping through records is slow and in a trigger can bring your entire system to a halt while you loop through the million records that hada 10% price increase.</p>
    singulars
    1. This table or related slice is empty.
    1. POAdvice on data capture management system
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USHLGEM
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAdvice on data capture management system
      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. VTAcceptedByOriginator
    1. COThanks for the detailed reply. I would be using MySql 5, which has support for triggers. I've not used triggers before but for this application they seem like a good way of handling changes to data. If I understand correctly, in my case the trigger would most likely be activated on an UPDATE to a record - handling a chain of db events exclusively at the db end, thereby reducing PHP application code to handle these events. Also, the idea of audit tables sounds reasonable, with extra columns for logging dates etc.
      singulars
      1. PO
      1. USkenny99
 

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