Databases
StackOverflow2013
Postsdbo
POStoring and retrieving historical data using SQL / relational database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStoring and retrieving historical data using SQL / relational database
    primarykey
    data
    text
    <p>Given this table:</p> <pre><code>CREATE TABLE DeptPeopleHistory ( DEPT_ID INTEGER, PERSON_ID INTEGER, START_DATE INTEGER, END_DATE INTEGER, UNIQUE(DEPT_ID, START_DATE, PERSON_ID), -- works as sorted index. UNIQUE(PERSON_ID, START_DATE), UNIQUE(PERSON_ID, END_DATE), CONSTRAINT (START_DATE &lt; END_DATE) ); </code></pre> <p>I have two needs. The first is to get all people that works on a given department at a given date. Currently I use this (semantically correct) query:</p> <pre><code>SELECT PERSON_ID FROM DeptPeopleHistory WHERE DEPT_IT = :given_dept AND START_DATE &lt;= :given_date AND :given_date &lt; END_DATE </code></pre> <p>This is fast for small history table or querying recent data, but is slow for big history tables and old data, because the optimizer uses only the first index and there's no good way to deal with END_DATE. I've tried to add END_DATE to the first index, but query performance is the same. I guess it's because the sub-filter (DEPT_IT=:given_dept AND START_DATE &lt;= :given_date) when applied to a sorted index (DEPT_ID, START_DATE, END_DATE, PERSON_ID) results in data with unsorted END_DATE, so (:given_date &lt; END_DATE) still requires a sequential scan on the result.</p> <p>My other need is to enforce the following constraint: a person cannot work at two departments at same time, nor twice at the same department. This means the following:</p> <pre><code>-- This must work for previously empty data: INSERT INTO DeptPeopleHistory(DEPT_ID, PERSON_ID, START_DATE, END_DATE) VALUES (1, 1, 20100501, 20100520); -- This should cause constraint violation because the person already -- works at dept 1 on days from 20100517 to 20100519: INSERT INTO DeptPeopleHistory(DEPT_ID, PERSON_ID, START_DATE, END_DATE) VALUES (:any_dept, 1, 20100517, 20100523); </code></pre> <p>Another way to specify this constraint, is that for a given PERSON_ID, START_DATE must be the minimum or equals to END_DATE from another record.</p> <p>Looking at those two needs, we actually need an efficient way for dealing with non-intersected ranges. Do you know some feature or construct in generic SQL or some specific database than can deal with these needs? Perhaps some "spatial database" feature?</p> <p>The examples are in MySQL, but I need solutions that work on Oracle, SQL Server and FireBird. The solutions don't need to be portable across all such databases.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfernacolo
    1. USfernacolo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStoring and retrieving historical data using SQL / relational database
      1. USKarlsFriend
      1. VTFavorite
    2. VO
      singulars
      1. POStoring and retrieving historical data using SQL / relational database
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFirst, mySQL lacks the constraints required for temporal databases e.g. your table lacks a sequenced primary key i.e. allows overlapping dates for the same person + dept; even your `START_DATE < END_DATE` won't be checked when the table is updated. Second, (assuming you are looking for something open source), postgreSQL has better temporal support anyhow :)
      singulars
      1. POStoring and retrieving historical data using SQL / relational database
      1. USonedaywhen
 

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