Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Would it be possible to change the structure of table <code>DeptPeopleHistory</code> to?:</p> <pre><code>CREATE TABLE DeptPeopleHistoryDetail ( DEPT_ID INTEGER, PERSON_ID INTEGER, WORK_DATE INTEGER, --- why is that INT and not DATE by the way? UNIQUE(WORK_DATE, PERSON_ID) ); </code></pre> <p>Pros:</p> <ul> <li>You don't need to enforce any of the previous <code>UNIQUE</code> constraints, nor the <code>START_DATE &lt; END_DATE</code> one.</li> <li>The second complex constraint(s) are magically solved too.</li> </ul> <p>Cons:</p> <ul> <li>The <code>(1, 1, 20100501, 20100520)</code> from the previous example is now split into 20 rows. Not a real problem, I'd say. Relational databases are designed to handle many rows.</li> <li>To find <code>START_DATE</code> or <code>END_DATE</code> for a person in a department, a query has to be run. (if that is too slow, which I doubt, an additional table can be used)</li> </ul> <hr> <p>Oh, and your slow query would be written as:</p> <pre><code>SELECT PERSON_ID FROM DeptPeopleHistoryDetail WHERE DEPT_IT = :given_dept AND WORK_DATE = :given_date </code></pre> <hr> <p>With your current <code>DeptPeopleHistory</code> design, can you try the performance of the following query? </p> <pre><code>SELECT H.PERSON_ID FROM DeptPeopleHistory H JOIN ( SELECT PERSON_ID , MAX(START_DATE) AS LATEST_START_DATE FROM DeptPeopleHistory WHERE DEPT_IT = :given_dept AND START_DATE &lt;= :given_date GROUP BY PERSON_ID ) AS grp ON H.DEPT_IT = :given_dept AND grp.PERSON_ID = H.PERSON_ID AND grp.LATEST_START_DATE = H.START_DATE WHERE :given_date &lt; H.END_DATE </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POStoring and retrieving historical data using SQL / relational database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USypercubeᵀᴹ
    1. USypercubeᵀᴹ
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMight work for short periods, but I don't think it's scalable. Consider 5 years of 1000 employees - 1.8M rows - too much for a single table. Also, we might change precision from day to minutes, and that would multiply rows by 1440. By the way, I used INT instead of DATE just to get portable literals and make the example independent of data type.
      singulars
      1. PO
      1. USfernacolo
    2. CO@fernacolo: 1.8M rows x 12 bytes = 21MB. Not really big. If you have 30 years and 100K employees, yeah, it would get big.
      singulars
      1. PO
      1. USypercubeᵀᴹ
    3. COIf you increase precision to minutes, it's out of the question, too.
      singulars
      1. PO
      1. USypercubeᵀᴹ
 

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